Student Programs
Student programs track wether a not a student was in a program, such as, lunch programs, instructional programs, gate programs, etc., for a specific date range.
public.student_program_aff
This is the primary table for tracking student programs.
Columns
SELECT
student_id,
student_program_id,
start_date,
end_date,
FROM public.student_program_aff
LIMIT 10
Gotchas
end_date
will beNULL
if the student has not exited the programstart_date
can beNULL
, but it is generally considered bad data
Examples
-- Get a list of students in a lunch program today
SELECT
students.local_student_id,
students.first_name,
students.last_name
FROM students
WHERE students.student_id IN (
SELECT spa.student_id
FROM student_program_aff AS spa
WHERE spa.student_program_id IN (
SELECT sp.code_id
FROM codes.student_programs AS sp
WHERE sp.system_key IN ('lunch_free_1', 'lunch_free_reduced')
)
AND spa.start_date <= current_date
AND (current_date <= spa.end_date OR spa.end_date IS NULL)
)
LIMIT 100
Want some more examples? Send me some suggestions of the data you would like to see zach@illuminateed.com