SQLTeam.com | Weblogs | Forums

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

sql2008r2

#1

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


#2

I'm not 100% sure what you're trying to achieve here.

In your test data the last rows -

2014 Week 10 - 2014 Week 13 are all abs_status 1 (absent)

As that whole month is therefore absence, should every row previous to this be marked as "dropout" (-1)?


#3

YES - 100% Correct

Output should be

Month Week Status
2013/01 2013 Week 01 -1
2013/01 2013 Week 02 -1
2013/01 2013 Week 03 -1
2013/01 2013 Week 04 0
2013/01 2013 Week 05 -1
2013/02 2013 Week 06 -1
2013/02 2013 Week 07 -1
2013/02 2013 Week 08 -1
2013/02 2013 Week 09 0
2013/03 2013 Week 10 0
2013/03 2013 Week 11 0
2013/03 2013 Week 12 0
2013/03 2013 Week 13 0
2013/04 2013 Week 14 -1
2013/04 2013 Week 15 -1
2013/04 2013 Week 16 -1
2013/04 2013 Week 17 0
2013/05 2013 Week 18 -1
2013/05 2013 Week 19 -1
2013/05 2013 Week 20 -1
2013/05 2013 Week 21 0
2013/05 2013 Week 22 -1
2013/06 2013 Week 23 -1
2013/06 2013 Week 24 -1
2013/06 2013 Week 25 0
2013/06 2013 Week 26 0
2013/07 2013 Week 27 -1
2013/07 2013 Week 28 -1
2013/07 2013 Week 29 -1
2013/07 2013 Week 30 0
2013/08 2013 Week 31 -1
2013/08 2013 Week 32 -1
2013/08 2013 Week 33 0
2013/08 2013 Week 34 -1
2013/08 2013 Week 35 -1
2013/09 2013 Week 36 -1
2013/09 2013 Week 37 -1
2013/09 2013 Week 38 -1
2013/09 2013 Week 39 0
2013/10 2013 Week 40 -1
2013/10 2013 Week 41 -1
2013/10 2013 Week 42 -1
2013/10 2013 Week 43 0
2013/10 2013 Week 44 -1
2013/11 2013 Week 45 -1
2013/11 2013 Week 46 -1
2013/11 2013 Week 47 -1
2013/11 2013 Week 48 0
2013/12 2013 Week 49 -1
2013/12 2013 Week 50 -1
2013/12 2013 Week 51 0
2013/12 2013 Week 52 -2
2013/12 2013 Week 53 -2
2014/01 2014 Week 01 -2
2014/01 2014 Week 02 -2
2014/01 2014 Week 03 -2
2014/01 2014 Week 04 -2
2014/01 2014 Week 05 -2
2014/02 2014 Week 06 -2
2014/02 2014 Week 07 -2
2014/02 2014 Week 08 0
2014/02 2014 Week 09 -2
2014/03 2014 Week 10 -2
2014/03 2014 Week 11 -2
2014/03 2014 Week 12 -2
2014/03 2014 Week 13 -2


#4

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.

You could adjust the select to an update


#5

Hi Dohsan

Thank you so much for simplifying that Code.

Its works perfectly except for

'2013 Week 52' to '2014 Week 07' - All should be -2 based on my rules above.

and

'2014 Week 09' to '2014 Week 13' - All should be -2 based on my rules above.