Purpose: Correct attendance duration issues in Oakland programs caused by incorrect software usage during check-in.
Summary
Fixed ALL attendance duration issues across Oakland programs in two phases:
Phase A: Negative Duration Fix
- Brookfield Soccer Fall 2025 AM: 1,644 → 0 negative records
- Flag Football League Fall 2025 PM: 90 → 0 negative records
- Highland Soccer Fall 2025 PM: 5 → 0 negative records
- Skateboarding & Roller-skating Fall 2025 AM: 3 → 0 negative records
- Basketball Fall 2025 PM: 2 → 0 negative records
- Skateboarding & Roller-skating Fall 2025 PM: 1 → 0 negative records
Phase B: Excessive Duration Fix (>1 Day)
- Brookfield Soccer Fall 2025 AM: 278 records fixed
- Flag Football League Fall 2025 AM: 33 records fixed
- YES! We Play Day - Oakland Fall 2025: 32 records fixed
- Skateboarding & Roller-skating Fall 2025 PM: 1 record fixed
For: Oakland Impact: Restored accurate attendance data for 100% of affected records (471 negative + 344 excessive = 815 total) Status: COMPLETE
Problem
Attendance records had incorrect durations because:
- Staff checked students in retroactively (sometimes days later)
start_timecaptured the check-in timestamp, not actual attendance timeend_timewas set to the intended checkout time on the session date- Result: Either
end_time<start_time(negative duration) OR duration spanning multiple days
Example: Student attended 10/18 session, but staff checked them in on 10/21 at 7:39 PM with end_time of 10/18 at 4:00 PM = -4,539 minutes
Solution Approach
Phase 1: Brookfield Soccer (Initial Fix)
Used reconciliation file (brookfield-reconcilation) mapping student home school + gender to correct time slots:
| Time Slot | Schools | Gender |
|---|---|---|
| 8:30-10:00 AM | Greenleaf, Brookfield, Madison Primary, La Escuelita, East Oakland Pride, Acorn Woodland, Bella Vista | Girls |
| 9:45-11:15 AM | Greenleaf, Brookfield, Allendale, Bella Vista, Esperanza (Boys) + Manzanita Community, ICS, TCN (Girls) | Mixed |
| 11:00 AM-12:30 PM | La Escuelita, ICS, TCN, Manzanita Community (Boys) + Grass Valley, Fred T. Korematsu, EnCompass (COED) | Mixed |
| 12:15-1:45 PM | Madison Primary, Acorn Woodland (Boys) + Cleveland, Lincoln (COED) | Mixed |
| 1:30-3:00 PM | Prescott, MLK, Oakland Academy, REACH, Sankofa (COED) + Hoover/Esperanza (Girls) + Hoover/EOP (Boys) | Mixed |
Phase 2: Remaining Records (Final Fix)
For the 370 Brookfield Soccer records from schools not in the reconciliation file:
- Used gender-agnostic approach: look up school name only, ignore gender
- For schools not in CSV at all: assigned default time slot (9:45 AM - 11:15 AM)
For non-Brookfield programs (101 records):
- Set attendance
start_timeandend_timeto match the session’sstart_timeandend_time
Phase 3: Excessive Duration Fix (>1 Day)
Found 344 attendance records with duration exceeding 1 day (1,440 minutes). These were caused by the same retroactive check-in issue but resulted in extremely long positive durations rather than negative ones.
Fix applied:
UPDATE attendance a
SET start_time = s.start_time, end_time = s.end_time, updated_at = NOW()
FROM session s
WHERE a.session_id = s.id
AND EXTRACT(EPOCH FROM (a.end_time - a.start_time))/60 > 1440Records fixed by program:
| Program | Records Fixed |
|---|---|
| Brookfield Soccer Fall 2025 AM | 278 |
| Flag Football League Fall 2025 AM | 33 |
| YES! We Play Day - Oakland Fall 2025 | 32 |
| Skateboarding & Roller-skating Fall 2025 PM | 1 |
| TOTAL | 344 |
SQL Scripts
Created multi-step scripts:
- Delete duplicates with valid record - Remove negative records where student already has positive attendance for same session
- Delete duplicate negatives - Keep oldest negative per student/session using
DISTINCT ON - Update Brookfield Soccer - Apply time slot mapping (gender-specific then gender-agnostic fallback)
- Update other programs - Set attendance times to session times
- Fix excessive durations - Set attendance times to session times for records >1 day
Key technical decisions:
- Used subquery approach for UPDATE to avoid PostgreSQL FROM-clause reference limitations
- Handled NULL gender: use COED slot if exists, otherwise default to Male time slot
- Handled unique constraint
attendance_session_minute_uniqueby deleting duplicates first
Final Results
Negative Duration Fix
| Program | Before | After |
|---|---|---|
| Brookfield Soccer Fall 2025 AM | 1,644 | 0 |
| Flag Football League Fall 2025 PM | 90 | 0 |
| Highland Soccer Fall 2025 PM | 5 | 0 |
| Skateboarding AM | 3 | 0 |
| Basketball Fall 2025 PM | 2 | 0 |
| Skateboarding PM | 1 | 0 |
| TOTAL | 1,745 | 0 |
Excessive Duration Fix (>1 Day)
| Program | Before | After |
|---|---|---|
| Brookfield Soccer Fall 2025 AM | 278 | 0 |
| Flag Football League Fall 2025 AM | 33 | 0 |
| YES! We Play Day - Oakland Fall 2025 | 32 | 0 |
| Skateboarding PM | 1 | 0 |
| TOTAL | 344 | 0 |
Brookfield Soccer Verification
| Category | Count |
|---|---|
| Positive (90 min) | 1,633 |
| Other positive | 1,061 |
| Negative | 0 |
| Zero | 0 |
Files
/Users/josephfantini/projects/oakland/negative-minutes-correction/fix_brookfield_soccer_attendance.sql- Phase 1 transaction script/Users/josephfantini/projects/oakland/negative-minutes-correction/fix_remaining_negative_attendance.sql- Phase 2 script for remaining records/Users/josephfantini/projects/oakland/negative-minutes-correction/brookfield-reconcilation- Original time slot mapping CSV
Related: Hub