#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2015
    Posts
    1
    Rep Power
    0

    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

    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] <> '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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Stevens Point, Wisconsin
    Posts
    623
    Rep Power
    21

    Lightbulb


    Let me check it out and see if I can help
    *thinks*

    Your online demo setup is awesome!
    The solution you're trying appears more than what would be needed to accomplish the result.

    How about trying a HAVING clause?
    Last edited by wordracr; August 25th, 2015 at 08:51 PM.

IMN logo majestic logo threadwatch logo seochat tools logo