Updated PS Export Query

In a previous post I mentioned how our district was exporting data from PowerSchool for Hapara.  Since SQL is one of those things I just know enough of to get other stuff done the query was pretty ugly.  Since then I have updated it to pull the term abbreviation automatically.  It’s possible to optimize the query some more and you could update it to automatically pull the current term range but I still wanted to hard code that in since it will only need changed once a year and there’s no accidental sending of a new year data immediately after rollover to an old Hapara class configuration.

This query also joins with SectionTeacher and SchoolStaff in hopes of being able to extract the co-teachers one day….

SELECT DISTINCT
replace(replace(replace(Courses.course_name, ‘ ‘,”), ‘-‘, ”), ‘/’, ”) ||
case when Sections.TermID = 2600 then ‘YR’ else Terms.Abbreviation end || ‘-‘ ||
Sections.section_number || ‘-1617’ as mailbox,
replace(replace(replace(Courses.course_name, ‘ ‘,”), ‘-‘, ”), ‘/’, ”) ||
case when Sections.TermID = 2600 then ‘YR’ else Terms.Abbreviation end || ‘-‘ ||
Sections.section_number || ‘-1617’ as name,
Teachers.email_addr as teacher,
replace(Courses.course_name, ‘,’,”) as subjectfolder,
‘any’,
replace(replace(replace(Courses.course_name, ‘ ‘,”), ‘-‘, ”), ‘/’, ”) ||
case when Sections.TermID = 2600 then ‘YR’ else Terms.Abbreviation end || ‘-‘ ||
Sections.section_number || ‘-1617’ as calendar,
‘any’,
Sections.SchoolID
FROM
Sections
JOIN
SectionTeacher
ON
SectionTeacher.SectionID = Sections.ID
JOIN
SchoolStaff
ON
SchoolStaff.ID = SectionTeacher.TeacherID
JOIN
Users
ON
Users.DCID = SchoolStaff.Users_DCID
JOIN
Terms
ON
Terms.ID = Sections.TermID
JOIN
Courses
ON
Sections.course_number = courses.course_number
JOIN
Teachers
ON
Teachers.id = Sections.teacher
LEFT JOIN
U_DEF_EXT_SECTIONS s2
ON
Sections.DCID = s2.SectionsDCID
WHERE
Sections.termid >= 2600
AND
Sections.termid < 2699
AND
LENGTH(TRIM (Users.Email_addr)) > 0
AND
s2.upload = 1

Advertisements
Updated PS Export Query

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s