2

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.

Daniel
  • 145
  • 1
  • 13

3 Answers3

0

When you search PowerSchool from the admin website, it only returns active students regardless of what term you select. In other words, I can't select "2000-2001" from the terms list and magically have everything reflect that term.

I can think of a couple things that may be affecting your searches:

  • search by using a forward slash to include inactive students "/lastname"
  • if you are searching HS students - remember that seniors transfer to a special school when they graduate

A better way to verify your query results would be to use the ADA/ADM section under System Reports. Reduce your query to one date and then run the ADA/ADM by Date report for the same day.

Cole Tarbet
  • 160
  • 12
  • While I appreciate your help, the problem is beyond using the admin site. I was using Oracle developer to write the query because this is meant to run from a script. Also, I was already using the ADA/ADM section by querying the ps_adaadm_defaults_all view. Thanks anyway, though. I appreciate SOMEONE taking a stab at it. – Daniel Feb 02 '17 at 14:12
  • Oh, well I meant it as a solution to making your numbers match when verifying. You probably have the correct script results and incorrect admin site verification numbers. – Cole Tarbet Feb 02 '17 at 20:01
0

It sounds to me like you have two questions:

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

  1. 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.

  1. 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.

jmm
  • 16
  • 2
  • Thanks, that was helpful. I didn't realize that I could check it that way in the admin site. That was the real part that I was having difficulty with as I didn't know how to check my results. Thanks again! – Daniel Feb 13 '17 at 17:50
  • You're welcome! I'd recommend keeping that report on hand; principals and other admins often are looking for exactly that and are easily impressed by its usability. – jmm Feb 14 '17 at 17:41
0

Just came across this and for anyone that is reading through this, PowerSchool has come up with "as of" selections.

Examples:

*as_of=09/30/2020;enroll_status=0

*as_of=09/30/2020;track=D

Brad Mills
  • 1