I have an attendance table with attendance per week either "Present" or "Absent"
and need to have it changed as follows with reasons attached:
If a person does not attend for an entire month he is changed to a "DROPOUT" for the weeks he/she was absent before that month until the time he re-attended.
I've tried using SQL that check for "Streaks" checking for months with Combined Attendances but cannot get the "Dropped out from" to be correct. Any help will be highly appreciated.
Using Sample SourceData having 1 as "Absent" & 0 as "Present" , I've used the following SQL but have some bugs
/* Calculate RunGroup */
SELECT WeekNum, Abs_Status, (SELECT COUNT(*) FROM [SourceData] G WHERE G.Abs_Status <> GR.Abs_Status AND G.WeekNum <= GR.WeekNum) as RunGroup
INTO [RunGroup]
FROM [SourceData] GR;
GO
/* Determine how many weeks in each run */
SELECT Abs_Status, MIN(WeekNum) as StartDate, MAX(WeekNum) as EndDate, COUNT(*) as Games
INTO [WeeksinRun]
FROM [RunGroup] A GROUP BY Abs_Status, RunGroup ORDER BY Min(WeekNum);
GO
/****** What to mark as Dropouts ******/
SELECT [StartDate]
,[EndDate]
INTO [WeekstoUpd]
FROM [WeeksinRun] a,[SourceData] b, [SourceData] c
where a.[StartDate] = b.[Weeknum]
and a.[EndDate] = c.[Weeknum]
and b.[MONTH] <> c.[MONTH]
and a.Abs_Status = '1'
and a.[StartDate] <> '2013 Week 01';
GO
/****** Update Dropout Weeks ******/
update [SourceData]
set [SourceData].[Abs_Status] = '-2'
FROM [SourceData],[WeekstoUpd]
where [WeekNum]>=[StartDate] and [WeekNum]<=[EndDate];
GO
/****** Update Absent Weeks ******/
UPDATE [SourceData]
SET [Abs_Status] = '-1'
FROM [SourceData]
WHERE [SourceData].[Abs_Status] = '1';
GO
Was playing around with some ideas, left some of the extra columns in for you to play around
WITH Base
AS
(
SELECT sd.PersonID,
SD.WeekNum,
SD.MONTH,
SD.Abs_Status,
CountByMonth = COUNT(SD.Abs_Status) OVER (PARTITION BY PersonID,MONTH),
CountByStatus = COUNT(SD.Abs_Status) OVER (PARTITION BY PersonID,MONTH,ABS_STATUS),
CountComp = COUNT(SD.Abs_Status) OVER (PARTITION BY PersonID,MONTH) - COUNT(SD.Abs_Status) OVER (PARTITION BY PersonID,MONTH,ABS_STATUS)
FROM #SourceData AS SD
), LatestFullAbsenceMonth
AS
(
SELECT B.PersonID,
DropMonth = MAX(MONTH)
FROM Base AS B
WHERE B.Abs_Status = 1
AND B.CountComp = 0
GROUP BY B.PersonID
)
SELECT SD.WeekNum,
SD.MONTH,
CASE
WHEN SD.Abs_Status = 1 AND SD.MONTH = A.DropMonth THEN -2
WHEN SD.Abs_Status = 1 AND SD.MONTH < A.DropMonth THEN -1
ELSE SD.Abs_Status
END
FROM #SourceData AS SD
LEFT
JOIN LatestFullAbsenceMonth AS A
ON A.PersonID = SD.PersonID;
I assume there are multiple people on this so added a "PersonID". Idea was to find count by month and count by month by status. Find where all entries are -1 then find max of those months.