ChrisM@Work (4/3/2013)

---

Thanks for the ddl and dml, it makes a real difference. I'd recommend you split this into two steps. It will make the end result easier to understand, test and maintain than writing it as one query. Step 1 would be calculating the missing days, step 2 would be performing a running totals of the missing days and updating the new column. Here's Step 1. Check that the output is what you are expecting to see;

-- Step 1 Calculate missing days

SELECT s.*,

x.StartDate,

t.EndDate,

y.Missed

FROM TestScores s

INNER JOIN Terms t -- collect term end date

ON t.SchoolYR = s.SchoolYR

AND t.LocID = s.LocID

AND t.Term = s.Term

OUTER APPLY ( -- calculate a suitable term start date

SELECT StartDate = DATEADD(day,1,i.EndDate)

FROM Terms i

WHERE i.SchoolYR = t.SchoolYr

AND i.LocID = t.LocID

AND i.Term = t.Term-1

) x

CROSS APPLY ( -- aggregate missed days per ID / term

SELECT Missed = COUNT(*)/8

FROM Attend a

WHERE a.ID = s.ID

AND a.DateABS BETWEEN ISNULL(x.StartDate,'19000101') AND t.EndDate

) y

ORDER BY s.SchoolYR, s.ID, s.Term