Qualifying based on Sick time Before or After the Holiday - Query help please..

  • Hello Everyone,

    I have scenario where if an employee has a Sick right before or after the Holiday he doesn't get qualify for a Holiday Credit, if the Holiday falls on a Friday and he is Sick on next Monday then doesn't qualify.

    Here is the sample data.

    WITH SampleData (PERSON, [DATE], [PAYCODE],[HOLIDATE]) AS

    (

    -- First employee - Has Sick hours one day before Holiday so this employee does not qualify

    SELECT 101,'02/25/2014','REG','02/28/2014'

    UNION ALL SELECT 101,'02/26/2014','REG','02/28/2014'

    UNION ALL SELECT 101,'02/27/2014','SIC','02/28/2014'

    -- Second employee - Has Sick hours one day after the Holiday so this employee does not qualify

    UNION ALL SELECT 102,'02/25/2014','REG','02/28/2014'

    UNION ALL SELECT 102,'02/26/2014','REG','02/28/2014'

    UNION ALL SELECT 102,'02/27/2014','REG','02/28/2014'

    UNION ALL SELECT 102,'03/01/2014','SIC','02/28/2014'

    -- Third employee - Has Sick hours two days before the Holiday so this employee does qualify

    UNION ALL SELECT 103,'02/25/2014','REG','02/28/2014'

    UNION ALL SELECT 103,'02/26/2014','SIC','02/28/2014'

    UNION ALL SELECT 103,'02/27/2014','REG','02/28/2014'

    -- Fourth employee - Has No Sick hours one day before or one day after the Holiday so this employee does qualify

    UNION ALL SELECT 104,'02/25/2014','REG','02/28/2014'

    UNION ALL SELECT 104,'02/26/2014','REG','02/28/2014'

    UNION ALL SELECT 104,'02/27/2014','REG','02/28/2014'

    UNION ALL SELECT 104,'03/03/2014','REG','02/28/2014'

    -- Fifth employee - Has Sick hours on first working day after the Holiday so this employee does not qualify

    UNION ALL SELECT 105,'02/25/2014','REG','02/28/2014'

    UNION ALL SELECT 105,'02/26/2014','REG','02/28/2014'

    UNION ALL SELECT 105,'02/27/2014','REG','02/28/2014'

    UNION ALL SELECT 105,'03/03/2014','SIC','02/28/2014'

    )

    SELECT *

    FROM SampleData;

    Current Result

    PERSONDATE PAYCODEHOLIDATE

    10102/25/2014REG02/28/2014

    10102/26/2014REG02/28/2014

    10102/27/2014SIC02/28/2014

    10202/25/2014REG02/28/2014

    10202/26/2014REG02/28/2014

    10202/27/2014REG02/28/2014

    10203/01/2014SIC02/28/2014

    10302/25/2014REG02/28/2014

    10302/26/2014SIC02/28/2014

    10302/27/2014REG02/28/2014

    10402/25/2014REG02/28/2014

    10402/26/2014REG02/28/2014

    10402/27/2014REG02/28/2014

    10403/03/2014REG02/28/2014

    10502/25/2014REG02/28/2014

    10502/26/2014REG02/28/2014

    10502/27/2014REG02/28/2014

    10503/03/2014SIC02/28/2014

    In desired result, I only want the employee that qualified and only pass employee's number and the holiday date in the results.

    PERSONDATE

    10302/28/2014

    10402/28/2014

  • If you've got a Calendar table you can use it but I've constructed one in line to solve this:

    WITH SampleData (PERSON, [DATE], [PAYCODE],[HOLIDATE]) AS

    (

    -- First employee - Has Sick hours one day before Holiday so this employee does not qualify

    SELECT 101,CAST('02/25/2014' AS DATE),'REG',CAST('02/28/2014' AS DATE)

    UNION ALL SELECT 101,'02/26/2014','REG','02/28/2014'

    UNION ALL SELECT 101,'02/27/2014','SIC','02/28/2014'

    -- Second employee - Has Sick hours one day after the Holiday so this employee does not qualify

    UNION ALL SELECT 102,'02/25/2014','REG','02/28/2014'

    UNION ALL SELECT 102,'02/26/2014','REG','02/28/2014'

    UNION ALL SELECT 102,'02/27/2014','REG','02/28/2014'

    UNION ALL SELECT 102,'03/01/2014','SIC','02/28/2014'

    -- Third employee - Has Sick hours two days before the Holiday so this employee does qualify

    UNION ALL SELECT 103,'02/25/2014','REG','02/28/2014'

    UNION ALL SELECT 103,'02/26/2014','SIC','02/28/2014'

    UNION ALL SELECT 103,'02/27/2014','REG','02/28/2014'

    -- Fourth employee - Has No Sick hours one day before or one day after the Holiday so this employee does qualify

    UNION ALL SELECT 104,'02/25/2014','REG','02/28/2014'

    UNION ALL SELECT 104,'02/26/2014','REG','02/28/2014'

    UNION ALL SELECT 104,'02/27/2014','REG','02/28/2014'

    UNION ALL SELECT 104,'03/03/2014','REG','02/28/2014'

    -- Fifth employee - Has Sick hours on first working day after the Holiday so this employee does not qualify

    UNION ALL SELECT 105,'02/25/2014','REG','02/28/2014'

    UNION ALL SELECT 105,'02/26/2014','REG','02/28/2014'

    UNION ALL SELECT 105,'02/27/2014','REG','02/28/2014'

    UNION ALL SELECT 105,'03/03/2014','SIC','02/28/2014'

    ),

    SickDays AS

    (

    SELECT PERSON, [DATE], [PAYCODE],[HOLIDATE]

    FROM SampleData

    WHERE PAYCODE = 'SIC'

    ),

    Calendar (n) AS

    (

    SELECT 0 UNION ALL

    SELECT TOP (SELECT MAX(ABS(DATEDIFF(day, [DATE], HOLIDATE))) FROM SickDays)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns

    )

    SELECT PERSON, [DATE]=[HOLIDATE]

    FROM SickDays a

    CROSS APPLY

    (

    SELECT n=COUNT(*)

    FROM Calendar

    WHERE n BETWEEN 1 AND ABS(DATEDIFF(day, [DATE], HOLIDATE)) AND

    DATENAME(dw, DATEADD(day, n*SIGN(DATEDIFF(day, [DATE], HOLIDATE)), [DATE])) NOT IN ('Saturday', 'Sunday')

    ) b

    WHERE n > 1

    UNION ALL

    SELECT PERSON, [HOLIDATE]

    FROM SampleData

    GROUP BY PERSON, [HOLIDATE]

    HAVING COUNT(CASE WHEN PAYCODE = 'SIC' THEN 1 END) = 0;

    Note that I haven't had my coffee yet this morning so there may be a better way.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Wonderful !!

    You are awesome Dwaine 🙂

    Thank you,

  • DiabloZA (3/3/2014)


    Wonderful !!

    You are awesome Dwaine 🙂

    Thank you,

    You're welcome. No e in my name though. 😀


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Oops sorry.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply