Attendly Vault

Query Updates for ASP BSP Program Name Variations

2 min read

2025-12-11: Query Updates for ASP/BSP Program Name Variations

For: SAUSD Impact: Fixed enrollment, waitlist, and attendance queries to correctly match both abbreviated (“ASP”/“BSP”) and full program names (“After School Program”/“Before School Program”), exclude all Engage 360 program variations, and ensure waitlist reports only show students waiting for Current or Future programs - eliminating 1,215 erroneous Past program waitlist records.

Context: SAUSD uses inconsistent naming conventions across programs - some use abbreviations like “ASP” and “BSP” while others use full names like “After School Program” and “Before School Program”. Additionally, various year-prefixed Engage 360 programs (e.g., “2025-2026 ASP Engage 360 Full Program”) needed exclusion from reporting. The waitlist query was incorrectly showing students waitlisted for Past programs that had already ended.

Implementation:

  • Updated program matching logic to include both LIKE '%ASP%' OR LIKE '%After School%' patterns across all queries
  • Simplified Engage 360 exclusion from multiple specific patterns to single NOT LIKE '%Engage 360%' pattern that catches all variations regardless of year prefix or exact naming
  • Fixed waitlist query to filter programs using the full status CASE statement logic (Current/Future/Past/Canceled) by wrapping it in parentheses and checking IN ('Current', 'Future') - correctly excluding 1,215 Past program waitlist records
  • Maintained existing enrollment exclusion logic to prevent showing students waitlisted for programs they’re already enrolled in (state=‘complete’)

Queries Updated:

  1. Enrollment Query - TK-K through 6th-8th grade bands for both ASP and BSP
  2. Waitlist Query - Same grade bands with added Current/Future program filtering
  3. Attendance/ADA Query - Average Daily Attendance calculations by grade level

Technical Details:

  • Program status determined by session start/end dates relative to CURRENT_DATE
  • Status logic: Current (has past sessions AND future sessions), Future (no sessions before today), Past (all sessions ended), Canceled (canceledTimestamp exists)
  • All queries exclude Engage 360 variations using wildcard pattern matching
  • Kennedy Elementary example: 2 TK-K students previously showing in waitlist for “2025-2026 ASP Engage 360 Full Program” now correctly excluded

Tags: sausd reporting query-optimization data-quality

Graph View