June 17, 2014 at 6:00 pm
Hi Everyone,
I don't know how to edit my Topic title since it's a little misleading.
I'm trying to find if the Saturdays worked in Bi-Weekly period is first or second, based on the Saturday occurrence the CODEID column value changes from 01 to another #, for example if it's first Saturday then CODEID changes from 01 to 02 and it's second Saturday then the CODEID changes from 01 to 03.
Below is my table, current result and desired results.
Thank you...
My Table:
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT * FROM SampleData
Current Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0001Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0001Saturday05/10/2014
Expected Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0002Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0003Saturday05/10/2014
June 17, 2014 at 7:39 pm
I think I just very carefully solved this using a 2012 solution... figured I would post it for people to tear apart...
the ROWS PRECEDING window function is new in 2012... sorry!
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT Person
, [Hours]
, Rate
, DOW
, [Date]
--, SatCount
, CodeID = '0' + CAST(1 + CASE WHEN DOW='Saturday' THEN
SUM([SatCount]) OVER (PARTITION BY Person
ORDER BY [Date]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ELSE 0 END AS CHAR(2))
FROM
(SELECT Person
, [Hours]
, Rate
, CodeID
, DOW
, [Date]
, SatCount = CASE WHEN DOW='Saturday' THEN 1 ELSE 0 END
FROM SampleData) x
June 17, 2014 at 10:16 pm
Hi There,
Oh man, I wish this client had SQL Server 2012, they are using SQL 2005 🙁
Thanks for the help though 🙂
Take care,
June 17, 2014 at 10:37 pm
pietlinden (6/17/2014)
I think I just very carefully solved this using a 2012 solution... figured I would post it for people to tear apart...the ROWS PRECEDING window function is new in 2012... sorry!
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT Person
, [Hours]
, Rate
, DOW
, [Date]
--, SatCount
, CodeID = '0' + CAST(1 + CASE WHEN DOW='Saturday' THEN
SUM([SatCount]) OVER (PARTITION BY Person
ORDER BY [Date]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ELSE 0 END AS CHAR(2))
FROM
(SELECT Person
, [Hours]
, Rate
, CodeID
, DOW
, [Date]
, SatCount = CASE WHEN DOW='Saturday' THEN 1 ELSE 0 END
FROM SampleData) x
Not bad;-) the only flaw is the absence of the correct set divisor, the partition must include the CODEID.
(edit) I may have jumped the gun here with the CODEID:w00t:
😎
June 17, 2014 at 10:46 pm
Two solutions, first one for 2005 and later, the second 2012 and later. The difference is that the first one uses self-joining which makes it less efficient. For very large sets on earlier versions, I would suggest looking at quirky updates[/url].
😎
Solution for 2005 and later.
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
/* 2005 AND LATER */
SELECT
SD.PERSON
,SD.HOURS
,SD.RATE
,SD.CODEID+ ISNULL(SAD.SAT_COUNT,0) AS CODEID
,SD.DOW
,SD.DATE
FROM SampleData SD
LEFT OUTER JOIN
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SD.PERSON
,SD.CODEID
ORDER BY SD.DATE
) SAT_COUNT
,SD.PERSON
,SD.CODEID
,SD.DATE
FROM SampleData SD
WHERE SD.DOW = 'Saturday'
) AS SAD
ON SD.DATE = SAD.DATE
AND SD.PERSON = SAD.PERSON
AND SD.CODEID = SAD.CODEID
Solution for 2012 and later
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
/* 2012 AND LATER */
SELECT
SD.PERSON
,SD.HOURS
,SD.RATE
,SD.CODEID +
CASE
WHEN SD.DOW = 'Saturday' THEN
SUM(CASE
WHEN SD.DOW = 'Saturday' THEN 1
ELSE 0
END) OVER
(
PARTITION BY SD.PERSON
,SD.CODEID
ORDER BY SD.DATE
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)
ELSE 0
END AS CODEID
,SD.DOW
,SD.DATE
FROM SampleData SD;
Results
PERSON HOURS RATE CODEID DOW DATE
------- ------ ------ ------- --------- ----------
1234 7.00 40.00 1 Thursday 05/01/2014
1234 8.00 40.00 1 Friday 05/02/2014
1234 3.50 40.00 2 Saturday 05/03/2014
1234 3.50 40.00 1 Monday 05/05/2014
1234 8.00 40.00 1 Tuesday 05/06/2014
1234 3.50 40.00 1 Wednesday 05/07/2014
1234 3.50 40.00 1 Thursday 05/08/2014
1234 7.00 40.00 1 Friday 05/09/2014
1234 3.00 40.00 3 Saturday 05/10/2014
June 18, 2014 at 3:55 am
Eirikur Eiriksson (6/17/2014)
Two solutions, first one for 2005 and later, the second 2012 and later. The difference is that the first one uses self-joining which makes it less efficient. For very large sets on earlier versions, I would suggest looking at quirky updates[/url].
You do not really need to use self-join here even for SQL2005, this will be good enough:
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT
SD.PERSON
,SD.HOURS
,SD.RATE
,CASE WHEN SD.DOW != 'Saturday' THEN 1
ELSE ROW_NUMBER() OVER
(
PARTITION BY SD.PERSON
,SD.CODEID
,CASE WHEN SD.DOW = 'Saturday' THEN SD.DOW ELSE 'WD' END
ORDER BY SD.DATE
) + 1
END AS SAT_COUNT
,SD.DOW
,SD.DATE
FROM SampleData SD
ORDER BY SD.PERSON
,SD.CODEID
,SD.DATE
June 18, 2014 at 4:36 pm
Awesome, this is perfect.
Thank you very much everyone for all the help 🙂
June 20, 2014 at 1:05 pm
Are you just trying to determine the relative Saturday# within the month? If so, there are methods with vastly less difficulty and overhead.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 21, 2014 at 12:34 am
Hi There,
Actually the client changed the requirements, if it's the first Saturday between the selected dates then in CODEID column we would have a value of 450, for the second Saturday value would be 651. If the first Saturday was not worked then still the second Saturday should have a value of 451.
I'm not sure if it's doable to being with so that's why I didn't post anything back until you asked me 🙂
Example # 1, when both Saturdays are worked.
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT * FROM SampleData
Current Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0001Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0001Saturday05/10/2014
Desired Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.00450Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.00451Saturday05/10/2014
Example #2, when 1st Saturday is worked but not 2nd Saturday.
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
)
SELECT * FROM SampleData
Current Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0001Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
Desired Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.00450Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
Example #3, when 1st Saturday is not worked but the 2nd Saturday is.
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT * FROM SampleData
Current Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0001Saturday05/10/2014
Desired Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.00451Saturday05/10/2014
Thank you,
June 21, 2014 at 4:03 am
Here is a quick solution using a CALENDAR CTE
😎
USE tempdb;
GO
;WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
,DATE_RANGE AS
(
SELECT
MIN(SD.DATE) AS START_DATE
,DATEDIFF(DAY,MIN(SD.DATE),MAX(SD.DATE)) + 1 AS DATE_COUNT
FROM SampleData SD
)
,TN(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,CALENDAR AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY DR.START_DATE) + 449 AS CODEID
,CAST(DATEADD(DAY,NM.N,DR.START_DATE) AS DATE) AS SATUR_DAY
FROM DATE_RANGE DR
OUTER APPLY
(SELECT TOP (DR.DATE_COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM TN T1,TN T2,TN T3) NM(N)
WHERE DATENAME(WEEKDAY,DATEADD(DAY,NM.N,DR.START_DATE)) = N'Saturday'
)
SELECT
SD.PERSON
,SD.HOURS
,SD.RATE
,ISNULL(CA.CODEID,SD.CODEID) AS CODEID
,SD.DOW
,SD.DATE
FROM SampleData SD
LEFT OUTER JOIN CALENDAR CA
ON SD.DATE = CA.SATUR_DAY;
Results
PERSON HOURS RATE CODEID DOW DATE
------- ------ ------- -------- --------- ----------
1234 7.00 40.00 1 Thursday 05/01/2014
1234 8.00 40.00 1 Friday 05/02/2014
1234 3.50 40.00 450 Saturday 05/03/2014
1234 3.50 40.00 1 Monday 05/05/2014
1234 8.00 40.00 1 Tuesday 05/06/2014
1234 3.50 40.00 1 Wednesday 05/07/2014
1234 3.50 40.00 1 Thursday 05/08/2014
1234 7.00 40.00 1 Friday 05/09/2014
1234 3.00 40.00 451 Saturday 05/10/2014
June 22, 2014 at 9:35 pm
Yep, works like a charm 🙂
Thank you very much Sir !!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply