March 2, 2014 at 2:42 pm
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
March 2, 2014 at 6:17 pm
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 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
March 3, 2014 at 12:21 am
Wonderful !!
You are awesome Dwaine 🙂
Thank you,
March 3, 2014 at 12:23 am
DiabloZA (3/3/2014)
Wonderful !!You are awesome Dwaine 🙂
Thank you,
You're welcome. No e in my name though. 😀
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
March 3, 2014 at 12:50 am
Oops sorry.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply