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 Code

Solution

Not sure if I understood this correctly, but maybe this is what you want:

update s 
set s.Abs_Status = 2
from SourceData S
where not exists (
  select 1 from SourceData S2
  where S2.Abs_Status = 0 and S2.MONTH = S.MONTH
) and Abs_status = 1

update s 
set s.Abs_Status = 2
from SourceData S
where 2 = (
  select top 1 S2.Abs_Status from SourceData S2
  where S2.Abs_Status <> 1 and S2.WeekNum > S.WeekNum
  order by S2.WeekNum asc
) and Abs_status = 1

update s 
set s.Abs_Status = 2
from SourceData S
where 2 = (
  select top 1 S2.Abs_Status from SourceData S2
  where S2.Abs_Status <> 1 and S2.WeekNum < S.WeekNum
  order by S2.WeekNum desc
) and Abs_status = 1

select * from [SourceData];

The first update will update Abs_Status to 2 for rows where the whole month is with Abs_Status = 1

The next 2 updates will update the partial months before / after this absence to 2.

SQL Fiddle