matt.j.garretson (7/29/2013)
This looks great! Oh man thank you so much! You don't realize how much you have helped me!!! For query 4 for I am looking for is: if a child has a status of enrolled in at the beginning of January or given a status of enrolled during the month of January does child stay enrolled for 181 days (6 months). So the child does not become "Deenrolled" of "Inactive" . Again, your assistance is great! thanks.
Glad to help ... For the fourth query you could add something like the following to the query
, Q4 AS (
-- Query 4
SELECT ID
FROM Status S
WHERE
--Enrolled_Date between '20130101' and '20130131' -- Enrolled in January
Enrolled_Date < '20130201' -- Enrolled before February
AND not exists (SELECT ID FROM Inactive I WHERE S.ID = I.ID)
AND not exists (SELECT ID FROM Deenrolled D WHERE S.ID = D.ID and D.Deenrolled_Date >= S.Enrolled_Date)
)
SELECT ...
Also as a note, if your query this query extends for more than a year then you will need group by the year as well.
SELECT ID,
DATEPART(yy,Attendence_date) YearNum,
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(yy,Attendence_date), DATEPART(wk,Attendence_date)
HAVING COUNT(*) > 1
Also you may want to put further statements in the WHERE clause for the Inactive and Deenrolled tables to ensure that there dates are in the time frame that you are querying.
There are quite a few little gotchas when working with dates and I'm definitely not expert on them, so please test these queries thoroughly
Edit: Changed y to yy in the datepart