SQL to determine if a non-attendance is a “DROPOUT”

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

SQL Fiddle Example

i’ve seen this post in two other forums

have you had an answer yet?

Yes… Thank you.

After weeks of trying to resolve this myself, I finally realised the power of forums.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.