April 10, 2012 at 11:47 am
I need to group together each work-day date for both the current month and the previous month. Further, I need each date for both months to list in ascending order and to correspond to a the sequential number from 0 to 22 in descending order (the number of work-dates). I am able to list a table of work-dates for each month but that doesnt really help and I have no idea how to accomplish this. Here is what the output would look like. I realize this looks like a homework assignment, but no, it's not (been out of school many years). Help is greatly appreciated!
-----March----- -----April-------
Date Weekday Date Weekday Grp#
----------------------------------------
000022
1Th0021
2F1W20
5M2Th19
6T3F18
7W6M17
8Th7T16
9F8W15
12M9Th14
13T10F13
14W13M12
15Th14T11
16F15W10
19M16Th9
20T17F8
21W20M7
22Th21T6
23F22W5
26M23Th4
27T24F3
28W27M2
29Th28T1
30F29W0
April 10, 2012 at 12:03 pm
Please read this article first:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
To know how to post your data in readily consumable format! We all are volunteers! We wont have full time to guess, compile and produce the output of your expectation. Please help us help you!
April 10, 2012 at 1:11 pm
Sorry about that.. This is how the output should be. Thank you.
SELECT '0' AS currentDATE,'0' AS currentWORKDAY,'0' AS PrevDate, '0' AS PreWORKDAY,'22' AS GroupNo UNION ALL
SELECT '1','Th','0','0','21' UNION ALL
SELECT '2','F','1','W','20' UNION ALL
SELECT '5','M','2','Th','19' UNION ALL
SELECT '6','T','3','F','18' UNION ALL
SELECT '7','W','6','M','17' UNION ALL
SELECT '8','Th','7','T','16' UNION ALL
SELECT '9','F','8','W','15' UNION ALL
SELECT '12','M','9','Th','14' UNION ALL
SELECT '13','T','10','F','13' UNION ALL
SELECT '14','W','13','M','12' UNION ALL
SELECT '15','Th','14','T','11' UNION ALL
SELECT '16','F','15','W','10' UNION ALL
SELECT '19','M','16','Th','9' UNION ALL
SELECT '20','T','17','F','8' UNION ALL
SELECT '21','W','20','M','7' UNION ALL
SELECT '22','Th','21','T','6' UNION ALL
SELECT '23','F','22','W','5' UNION ALL
SELECT '26','M','23','Th','4' UNION ALL
SELECT '27','T','24','F','3' UNION ALL
SELECT '28','W','27','M','2' UNION ALL
SELECT '29','Th','28','T','1' UNION ALL
SELECT '30','F','29','W','0'
April 10, 2012 at 1:30 pm
I still quite cant get my head wrapped aroound the logic. Can you please elaborate! I understand how u arrived at March's data (date and Weekday) but how did u do that for the April?
April 10, 2012 at 1:53 pm
Not sure what you're asking. I do add two necessary rows at the top as place holders when there are not 31 days in a month and therefore only 18 or 19 work days. everything is tied to the groupNO column, the far right column which is 0 through 22.
April 10, 2012 at 2:17 pm
March has the following working week days
dATE dAY
----------------------- ------------------------------
2012-03-01 00:00:00.000 Thursday
2012-03-02 00:00:00.000 Friday
2012-03-05 00:00:00.000 Monday
2012-03-06 00:00:00.000 Tuesday
2012-03-07 00:00:00.000 Wednesday
2012-03-08 00:00:00.000 Thursday
2012-03-09 00:00:00.000 Friday
2012-03-12 00:00:00.000 Monday
2012-03-13 00:00:00.000 Tuesday
2012-03-14 00:00:00.000 Wednesday
2012-03-15 00:00:00.000 Thursday
2012-03-16 00:00:00.000 Friday
2012-03-19 00:00:00.000 Monday
2012-03-20 00:00:00.000 Tuesday
2012-03-21 00:00:00.000 Wednesday
2012-03-22 00:00:00.000 Thursday
2012-03-23 00:00:00.000 Friday
2012-03-26 00:00:00.000 Monday
2012-03-27 00:00:00.000 Tuesday
2012-03-28 00:00:00.000 Wednesday
2012-03-29 00:00:00.000 Thursday
2012-03-30 00:00:00.000 Friday
April has these
dATE dAY
----------------------- ------------------------------
2012-04-02 00:00:00.000 Monday
2012-04-03 00:00:00.000 Tuesday
2012-04-04 00:00:00.000 Wednesday
2012-04-05 00:00:00.000 Thursday
2012-04-06 00:00:00.000 Friday
2012-04-09 00:00:00.000 Monday
2012-04-10 00:00:00.000 Tuesday
2012-04-11 00:00:00.000 Wednesday
2012-04-12 00:00:00.000 Thursday
2012-04-13 00:00:00.000 Friday
2012-04-16 00:00:00.000 Monday
2012-04-17 00:00:00.000 Tuesday
2012-04-18 00:00:00.000 Wednesday
2012-04-19 00:00:00.000 Thursday
2012-04-20 00:00:00.000 Friday
2012-04-23 00:00:00.000 Monday
2012-04-24 00:00:00.000 Tuesday
2012-04-25 00:00:00.000 Wednesday
2012-04-26 00:00:00.000 Thursday
2012-04-27 00:00:00.000 Friday
2012-04-30 00:00:00.000 Monday
Now tel me, with these data sets, how should we arrive at the "expected result" shown by you!
April 10, 2012 at 3:15 pm
I appreciate your patience. I'm trying to do too much at once. I had given you March and February. Here is April and March.
SELECT '0' AS currentDATE,'0' AS currentWORKDAY,'1' AS PrevDate, 'Th' AS PreWORKDAY,'22' AS GroupNo UNION ALL
SELECT '2','M','2','F','21' UNION ALL
SELECT '3','T','5','M','20' UNION ALL
SELECT '4','W','6','T','19' UNION ALL
SELECT '5','Th','7','W','18' UNION ALL
SELECT '6','F','8','Th','17' UNION ALL
SELECT '9','M','9','F','16' UNION ALL
SELECT '10','T','12','M','15' UNION ALL
SELECT '11','W','13','T','14' UNION ALL
SELECT '12','Th','14','W','13' UNION ALL
SELECT '13','F','15','Th','12' UNION ALL
SELECT '16','M','16','F','11' UNION ALL
SELECT '17','T','19','M','10' UNION ALL
SELECT '18','W','20','T','9' UNION ALL
SELECT '19','Th','21','W','8' UNION ALL
SELECT '20','F','22','Th','7' UNION ALL
SELECT '23','M','23','F','6' UNION ALL
SELECT '24','T','26','M','5' UNION ALL
SELECT '25','W','27','T','4' UNION ALL
SELECT '26','Th','28','W','3' UNION ALL
SELECT '27','F','29','Th','2' UNION ALL
SELECT '30','M','30','F','0'
April 10, 2012 at 3:19 pm
Something doesn't look right. The first column of dates goes from 2 to 3 with gaps. The second column goes from 21 to 2 with no gaps.
April 10, 2012 at 3:29 pm
Lynn, I don't see what you mean??
April 10, 2012 at 3:34 pm
I missed the middle column of numbers, sorry.
April 10, 2012 at 3:35 pm
Both April and March show work-dates (omitting Week-ends) M-F Dates only are shown.
April 10, 2012 at 4:14 pm
Somehting like this?
DECLARE @CurrentDate DATETIME = GETDATE()
,@PrevDate DATETIME
;
SELECT @CurrentDate = DATEADD(MM,DATEDIFF(MM,0,@CurrentDate),0)
;
SELECT @PrevDate = DATEADD(MM,-1,@CurrentDate)
;
;WITH WorkDayNumbers (N) AS
(
SELECT TOP 31 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master.sys.columns
)
, CurrMonthCTE AS
(
SELECT
RN = ROW_NUMBER() OVER(ORDER BY CrsApp1.Dt DESC)
, Dt = DATEPART(DAY,CrsApp1.Dt )
, Dy = CASE WHEN CrsApp2.Dy = 'Thursday' THEN 'Th'
ELSE LEFT(CrsApp2.Dy,1)
END
FROM WorkDayNumbers W
CROSS APPLY ( SELECT DATEADD(DD, W.N -1 , @CurrentDate) ) CrsApp1(Dt)
CROSS APPLY ( SELECT DATENAME( WEEKDAY ,CrsApp1.Dt) ) CrsApp2(Dy)
WHERE W.N BETWEEN 1
AND DATEDIFF(DD, @CurrentDate, DATEADD(MM,1,@CurrentDate))
AND CrsApp2.Dy IN ('Monday', 'Tuesday','Wednesday','Thursday','Friday')
)
, PrevMonthCTE AS
(
SELECT
RN = ROW_NUMBER() OVER(ORDER BY CrsApp1.Dt DESC)
, Dt = DATEPART(DAY,CrsApp1.Dt )
, Dy = CASE WHEN CrsApp2.Dy = 'Thursday' THEN 'Th'
ELSE LEFT(CrsApp2.Dy,1)
END
FROM WorkDayNumbers W
CROSS APPLY ( SELECT DATEADD(DD, W.N -1 , @PrevDate) ) CrsApp1(Dt)
CROSS APPLY ( SELECT DATENAME( WEEKDAY ,CrsApp1.Dt) ) CrsApp2(Dy)
WHERE W.N BETWEEN 1
AND DATEDIFF(DD, @PrevDate, DATEADD(MM,1,@PrevDate))
AND CrsApp2.Dy IN ('Monday', 'Tuesday','Wednesday','Thursday','Friday')
)
SELECT currentDATE = ISNULL ( CMC.Dt ,0)
,currentWORKDAY = ISNULL ( CMC.Dy ,0)
,PrevDate = ISNULL ( PMC.Dt ,0)
,PreWORKDAY = ISNULL ( PMC.Dy ,0)
,GroupNo = ISNULL (CMC.RN , PMC.RN)
FROM CurrMonthCTE CMC
FULL OUTER JOIN PrevMonthCTE PMC
ON CMC.RN = PMC.RN
ORDER BY GroupNo DESC
Produces output like:
currentDATE currentWORKDAY PrevDate PreWORKDAY GroupNo
----------- -------------- ----------- ---------- ---------
0 0 1 Th 22
2 M 2 F 21
3 T 5 M 20
4 W 6 T 19
5 Th 7 W 18
6 F 8 Th 17
9 M 9 F 16
10 T 12 M 15
11 W 13 T 14
12 Th 14 W 13
13 F 15 Th 12
16 M 16 F 11
17 T 19 M 10
18 W 20 T 9
19 Th 21 W 8
20 F 22 Th 7
23 M 23 F 6
24 T 26 M 5
25 W 27 T 4
26 Th 28 W 3
27 F 29 Th 2
30 M 30 F 1
April 10, 2012 at 4:40 pm
Wow! A great help. I beleive I can do what I need with this. Just need to join some row/column groupings of the current and previous month's data with the corresponding dates from each month. Thanks a bunch!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply