Hi
I hope I have understood your requirements for this. Thanks for posting some sample data, however it wasn't really enough to test this properly. Also the statements needed a bit of cleaning up to run.
I wasn't sure how you defined Served so I just did a count of distinct ids from attendance.
Query 4 wasn't very in it's definition, so I left that one out.
To try and keep things reasonably clear I have put the queries within a CTE then combined them in a single query at the end
WITH Served AS (
-- count from attendance. Change this to which query gets the number you want
SELECT COUNT(DISTINCT ID) ServedCount
FROM Attendance
)
,Q1 AS (
-- Query 1
SELECT ID,
DATEPART(wk,Attendence_date) WeekNum,
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(wk,Attendence_date)
HAVING COUNT(*) > 1
--ORDER BY ID, DATEPART(wk,Attendence_date)
)
,Q2 AS (
-- Query 2
SELECT ID,
DATEPART(wk,Attendence_date) WeekNum,
COUNT(*) AS TimesAttended
FROM Attendance A
WHERE Attendence_date between '20130101' 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(wk,Attendence_date)
HAVING COUNT(*) > 1
--ORDER BY ID, DATEPART(wk,Attendence_date)
)
,Q3 AS (
-- Query 3
SELECT ID,
DATEPART(wk,Attendence_date) WeekNum,
COUNT(*) AS TimesAttended
FROM Attendance A
WHERE Attendence_date between '20130101' and getdate()
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(wk,Attendence_date)
HAVING COUNT(*) > 1
--ORDER BY ID, DATEPART(wk,Attendence_date)
)
-- Put them together
SELECT ServedCount,
P1, CAST(P1 AS FLOAT) / ServedCount P1_PCT_SERVED,
P2, CAST(P2 AS FLOAT) / ServedCount P2_PCT_SERVED,
P3, CAST(P3 AS FLOAT) / ServedCount P3_PCT_SERVED
FROM Served
CROSS APPLY (SELECT COUNT(DISTINCT ID) p1 FROM Q1 ) Q1
CROSS APPLY (SELECT COUNT(DISTINCT ID) p2 FROM Q2 ) Q2
CROSS APPLY (SELECT COUNT(DISTINCT ID) p3 FROM Q3 ) Q3
Hope this helps