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
SQL Fiddle Example - http://sqlfiddle.com/#!3/5018c/2
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
Code:
/* 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] <> '2012 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
Comment