August 25, 2016 at 2:36 pm
I am getting last 4 week numbers in a query - say 31 thru 33.
How can I rename 31 as week 1 , 32 as week2 , 33 as week 3 and 334 as week4 so it dynamically keeps changing?
August 25, 2016 at 4:07 pm
sharonsql2013 (8/25/2016)
I am getting last 4 week numbers in a query - say 31 thru 33.How can I rename 31 as week 1 , 32 as week2 , 33 as week 3 and 334 as week4 so it dynamically keeps changing?
Do your dates wrap at the beginning of your (calendar/fiscal) year? That would cause problems with any calculations. Also, are there actual date fields that can be used instead?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 25, 2016 at 9:17 pm
We need more info but here's some code that may help:
-- (1) Some sample data, a sample calendar table for a specific year
IF OBJECT_ID('tempdb..#calendar') IS NOT NULL DROP TABLE #calendar;
DECLARE @year char(4) = 2016; -- let's pick a year to create a calendar for
WITH iTally(N) AS
(
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(x),
(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(x),
(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) c(x)
),
dateRange AS (SELECT startDate = CAST(@year+'0101' AS date))
SELECT
CalDate = ISNULL(DATEADD(DD,N,startDate),startDate),
Qtr = ISNULL(DATEPART(Q,DATEADD(DD,N,startDate)),0),
weekNbr = ISNULL(DATEPART(WW,DATEADD(DD,N,startDate)),0)
INTO #calendar
FROM dateRange, iTally
WHERE N < (DATEDIFF(DD,startDate,DATEADD(YY,1,startDate)));
-- test:
-- SELECT * FROM #calendar;
-- (2) Posible solution:
WITH cteCalendar AS
(
SELECT *, newWeekNbr = DENSE_RANK() OVER (PARTITION BY qtr ORDER BY weekNbr DESC)
FROM #calendar
)
SELECT CalDate, Qtr, oldWeekNbr = weekNbr, newWeekNbr = 5-newWeekNbr
FROM cteCalendar
WHERE newWeekNbr < 5
ORDER BY CalDate;
Haha.... I read the requirement as
I am getting last 4 week numbers in a quarter
-- Itzik Ben-Gan 2001
August 26, 2016 at 1:09 am
sharonsql2013 (8/25/2016)
I am getting last 4 week numbers in a query - say 31 thru 33.How can I rename 31 as week 1 , 32 as week2 , 33 as week 3 and 334 as week4 so it dynamically keeps changing?
Subtract 30?
Or, perhaps, (WeekNumber-1)%30 + 1?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2016 at 8:32 am
I should have ben a bit specific. Here... Instead of directly renaming , Can we say , The max week number is week4 and then max - 1 is wk3 and so on...
CREATE TABLE #tbl_data (
Time_Started DateTime,
);
INSERT INTO #tbl_data (
Time_Started
)
VALUES('2016-08-26 09:08:47.763'),
('2016-08-19 09:08:47.763'),
('2016-08-12 09:08:47.763'),
('2016-08-05 09:08:47.763')
With CTEfinal AS
(Select
CONCAT(DATEPART(wk,Time_Started),'-',DATEPART(YEAR,Time_Started)) as Week_Year1
,DATEPART(wk,Time_Started) as Week_No from
#tbl_data)
SELECt
*
FROM CTEfinal
August 26, 2016 at 8:41 am
This helped...
CASE WHEN Week_No = (Select MAX(Week_No) FROM CTEfinal) THEN 'Week4'
WHEN Week_No = (Select MAX(Week_No)-1 FROM CTEfinal) THEN 'Week3'
WHEN Week_No = (Select MAX(Week_No)-2 FROM CTEfinal) THEN 'Week2'
WHEN Week_No = (Select MAX(Week_No)-3 FROM CTEfinal) THEN 'Week1'
August 26, 2016 at 8:46 am
SELECT
Time_Started
,ROW_NUMBER() OVER (ORDER BY DATEPART(wk,Time_Started)) AS Week_No
FROM #tbl_data
John
August 26, 2016 at 9:00 am
Thanks John
August 26, 2016 at 9:14 am
Actually, DENSE_RANK would probably be a better choice than ROW_NUMBER. That way, if for any reason you have two dates that are in the same week, they won't appear in the result set as different week numbers.
John
August 26, 2016 at 9:19 am
John Mitchell-245523 (8/26/2016)
SELECTTime_Started
,ROW_NUMBER() OVER (ORDER BY DATEPART(wk,Time_Started)) AS Week_No
FROM #tbl_data
John
Now try it with this data
INSERT INTO #tbl_data (
Time_Started
)
VALUES('2017-01-06 09:08:47.763'),
('2016-12-30 09:08:47.763'),
('2016-12-23 09:08:47.763'),
('2016-12-16 09:08:47.763');
This is why I asked about wrapping and underlying dates. Use the underlying date instead of the week number for your order.
SELECT Time_Started, ROW_NUMBER() OVER( ORDER BY Time_Started) AS Week_No
FROM #tbl_data
Drew
Edit: Grabbed the wrong quote.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 26, 2016 at 9:28 am
There are pros and cons. Your query will return (possibly) unexpected results if there are gaps in the data or two dates in the same week. But yes, wrapping round year end is certainly something to take account of.
John
August 26, 2016 at 9:50 am
John Mitchell-245523 (8/26/2016)
There are pros and cons. Your query will return (possibly) unexpected results if there are gaps in the data or two dates in the same week. But yes, wrapping round year end is certainly something to take account of.John
Yes, but your solution also has the same problem with gaps in data, so mentioning it specifically in the context of my query seems a bit disingenuous. I can also handle multiple dates in the same week fairly easily. The main point is that using a cyclic measure for your ordering is usually not the desired result, although there may be cases where it is.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply