PowerSchool: How to query past enrollment

I know this is somewhat of a specialized question since only a small percentage of members will even have heard of PowerSchool, but it's hard to find help for this. Given a start date and end date, I need to run a query that will return the student ID's for all students who were enrolled in the District during that time period. If I could use that with 'WITH AS', I could add it to an attendance query 'Where' clause like below. This is what I've got so far, but I don't know how to check it's accuracy:

SELECT * FROM Students 
WHERE ID IN (
    SELECT studentid FROM ps_adaadm_defaults_all 
    WHERE schoolid IN ('16', '28', '40') 
    AND calendardate >= '1-May-15' 
    AND calendardate <= '31-May-15' 
    GROUP BY studentid)
ORDER BY LastFirst;

"ps_adaadm_defaults_all" is a PowerSchool View that is mainly for ADM so my assumption here is that if a student ID exists in ps_adaadm_defaults_all with a date between the two given dates, that student was enrolled at least that day regardless of attendance, correct? Any PowerSchool users out there that can lend a hand?

I get results with this query but when I try to verify the accuracy by using the PowerSchool site, the results aren't exactly the same. What I mean by using the site is I log in as district admin, set Term to 15-16 year, School to desired school and select students whose last names begin with 'A'. I then start comparing the list it provides with the students from the query results whose last names begin with 'A'. I am noticing though that there are names that I get with my query that are not showing on the site and I think it's due to their exitdate being prior to the current schoolyear. Those students were obviously enrolled at that time, but their names aren't in the PowerSchool results. I'm thinking because they're not enrolled anymore? Is there any way for me to test the accuracy of this query? Am I even on the right track? Thanks in advance.

Solution

It sounds to me like you have two questions:

What is the best query to return all students who were enrolled during a given time period?
What is the best way to check that my query is selecting all records it should be?

I'd use the ps_enrollment view for your query. It includes student id, school id, and start and end dates, so it can be used to find all students who were enrolled at a certain point in time.

Students enrolled for the whole specified time period

SELECT UNIQUE pe.StudentID
FROM ps_enrollment pe
WHERE pe.schoolid IN ('16', '28', '40') 
AND pe.EntryDate <= '05/01/2015' 
AND pe.ExitDate >= '05/30/2015'

Students enrolled at any time within the specified time period

SELECT UNIQUE pe.StudentID
FROM ps_enrollment pe
WHERE pe.schoolid IN ('16', '28', '40') 
AND (
    (pe.EntryDate <= '05/01/2015' AND pe.ExitDate >= '05/01/2015') 
    OR (pe.EntryDate <= '05/30/2015' AND pe.ExitDate >= '05/30/2015') 
    OR (pe.EntryDate >= '05/01/2015' AND pe.ExitDate <= '05/30/2015')
    )

In the above example, the three conditions inside the AND check for enrollments that either started before or on and ended after or on the first date, then the second date, and finally checks for any enrollments that happened in between the two dates.

Note: I used UNIQUE instead of GROUP BY. I think it fits what you're doing a little bit more.

The easiest way to check these numbers in the admin side is to use System Reports -> Membership and Enrollment -> Enrollment Summary by Date. This is an easy way to check enrolled students numbers at any point in time, and also will give you the list of those specific students. It includes inactive students.

You can double-check that it's working perfectly by entering a single date in your query instead of using a date range, and by checking that date against the Enrollment Summary by Date. When I did this for our district, my query pulled 7 extra records (out of over 7000), but upon investigation, all of those were due to bad reenrollment records, so it appears to be working correctly.