SQL to find days membership/attendance to date

351 views

Skip to first unread message

Robin Gage

unread,

Dec 3, 2013, 8:02:44 PM12/3/13

to ak-...@googlegroups.com

I have two sub-queries that I use to calculate the number of days membership and days attendance, they work great, but they look at the whole year. So they really can’t be used until school is over.

Anyone out there have something that will find days membership and attendance for students, for a specific date range? We use meeting attendance. ADM.MEMBERSHIPVALUE, not a table or field I am familiar with.

(SELECT SUM (ADM.MEMBERSHIPVALUE)

FROM PS_ADAADM_MEETING_PTOD ADM

WHERE ADM.STUDENTID = EN.STUDENTID

AND (ADM.CALENDARDATE >= EN.ENTRYDATE

AND ADM.CALENDARDATE < EN.EXITDATE)

GROUP BY ADM.STUDENTID) "Membership",

(SELECT SUM (ADM.ATTENDANCEVALUE)

FROM PS_ADAADM_MEETING_PTOD ADM

WHERE ADM.STUDENTID = EN.STUDENTID

AND (ADM.CALENDARDATE >= EN.ENTRYDATE

AND ADM.CALENDARDATE < EN.EXITDATE)

GROUP BY ADM.STUDENTID) "Attendance"

--
Robin Gage
PowerSchool Admin
Northwest Arctic Borough School District
907-442-1833
www.nwarctic.org

Robin Gage

unread,

Dec 3, 2013, 8:13:00 PM12/3/13

to ak-...@googlegroups.com

The introduction of SYSDATE in two key places appears to work. Unless anyone else out there has a cleaner way to do this.

(SELECT SUM (ADM.MEMBERSHIPVALUE)

FROM PS_ADAADM_MEETING_PTOD ADM

WHERE ADM.STUDENTID = EN.STUDENTID

AND (ADM.CALENDARDATE >= EN.ENTRYDATE

AND ADM.CALENDARDATE < SYSDATE)

GROUP BY ADM.STUDENTID) "Membership",

(SELECT SUM (ADM.ATTENDANCEVALUE)

FROM PS_ADAADM_MEETING_PTOD ADM

WHERE ADM.STUDENTID = EN.STUDENTID

AND (ADM.CALENDARDATE >= EN.ENTRYDATE

AND ADM.CALENDARDATE < SYSDATE)

GROUP BY ADM.STUDENTID) "Attendance"

--
Robin Gage
PowerSchool Admin
Northwest Arctic Borough School District
907-442-1833
www.nwarctic.org

Reply all

Reply to author

Forward

0 new messages