Ahh ... one of the tricky ones 🙂
Assuming that we also want to include the weeks were there is no attendance, I have put in a small tally table to create up to 60 weeks. Then I create a table containing all weeks for each participant. Once that is done you can group by the ID and filter it using a having clause on the averageWITH Tally AS (
-- Small tally table to use with weeks. 60 values to cover slightly more than a year
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS TinyTally(N)
)
,PeriodOfInterestYearAndWeeks AS (
-- Get the weeks number and years for the period that we are interest in
SELECT ID,
DATEPART(yy,DATEADD(wk,N - 1,'20120701')) dpYear,
DATEPART(wk,DATEADD(wk,N- 1,'20120701')) dpWeek
From Tally
CROSS APPLY (SELECT DISTINCT ID FROM #Attendance) a
WHERE DATEADD(wk,N - 1,'20120701') BETWEEN '20120701' and '20130630'
)
,Q1 AS (
SELECT ID,
DATEPART(yy,Attendence_date) dpYear,
DATEPART(wk,Attendence_date) dpWeek,
COUNT(*) AS TimesAttended
FROM #Attendance A
WHERE Attendence_date between '20120701' and '20130630'
AND not exists (SELECT ID FROM #Inactive I WHERE A.ID = I.ID)
AND not exists (SELECT ID FROM #Deenrolled D WHERE A.ID = D.ID)
GROUP BY ID, DATEPART(yy,Attendence_date), DATEPART(wk,Attendence_date)
)
SELECT p.ID
FROM PeriodOfInterestYearAndWeeks p
LEFT OUTER JOIN Q1 a ON p.dpYear = a.dpYear and p.dpWeek = a.dpWeek and p.ID = a.ID
GROUP BY p.ID
HAVING AVG(ISNULL(CAST(TimesAttended AS FLOAT),0)) > 2
The OUTER JOIN will cause the TimesAttended to be NULL for weeks without counts. The NULLs are changed to 0 for the AVG and CAST as float, otherwise a integer is returned.
There is probably a better (and faster) way to do this, but I've got to go at the moment. I'll see if I can spend a bit of time on it later.