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 be NULL if the student has not exited the program
  • start_date can be NULL, 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