October 10, 2017 at 7:36 am
All,
I have the following CTE:
DECLARE @SQLStr NVARCHAR(max)
declare @query AS NVARCHAR(MAX)
WITH sample AS (SELECT CAST('01/01/2017' AS DATETIME) AS dt
UNION ALL SELECT DATEADD(dd, 1, dt) FROM sample s
WHERE DATEADD(dd, 1, dt) <= CAST('10/10/2017' AS DATETIME))
select ','+ quotename( concat( datepart(year, convert(date,dt)) , '_', datepart(week, convert(date,dt)) )) FROM sample
group by concat( datepart(year, convert(date,dt)) , '_', datepart(week, convert(date,dt)) )
order by concat( datepart(year, convert(date,dt)) , '_', datepart(week, convert(date,dt)) )
OPTION (MAXRECURSION 1000)
set dateformat dmy
set datefirst 1
The output is as follows (abbreviated to reduce the post length):
,[2017_1]
,[2017_10]
,[2017_11]
,[2017_12]
,[2017_13]
,[2017_14]
,[2017_15]
,[2017_16]
,[2017_17]
,[2017_18]
,[2017_19]
,[2017_2]
It's seems to be sorting as characters. I would like it to sort by date so I get (again abbreviated):
,[2017_1]
,[2017_2]
,[2017_3]
Is there a way to achieve this? I wondered about some kind of cast? However it's not a valid date format to cast to and cast might not be the most efficient anyway?
I don't think it has anything to do with it being a CTE but I include all the code for completeness
Thanks
October 10, 2017 at 8:19 am
as1981 - Tuesday, October 10, 2017 7:36 AMAll,
I have the following CTE:
DECLARE @SQLStr NVARCHAR(max)
declare @query AS NVARCHAR(MAX)
WITH sample AS (SELECT CAST('01/01/2017' AS DATETIME) AS dt
UNION ALL SELECT DATEADD(dd, 1, dt) FROM sample s
WHERE DATEADD(dd, 1, dt) <= CAST('10/10/2017' AS DATETIME))
select ','+ quotename( concat( datepart(year, convert(date,dt)) , '_', datepart(week, convert(date,dt)) )) FROM sample
group by concat( datepart(year, convert(date,dt)) , '_', datepart(week, convert(date,dt)) )
order by concat( datepart(year, convert(date,dt)) , '_', datepart(week, convert(date,dt)) )
OPTION (MAXRECURSION 1000)
set dateformat dmy
set datefirst 1
The output is as follows (abbreviated to reduce the post length):
,[2017_1]
,[2017_10]
,[2017_11]
,[2017_12]
,[2017_13]
,[2017_14]
,[2017_15]
,[2017_16]
,[2017_17]
,[2017_18]
,[2017_19]
,[2017_2]It's seems to be sorting as characters. I would like it to sort by date so I get (again abbreviated):
,[2017_1]
,[2017_2]
,[2017_3]Is there a way to achieve this? I wondered about some kind of cast? However it's not a valid date format to cast to and cast might not be the most efficient anyway?
I don't think it has anything to do with it being a CTE but I include all the code for completeness
Thanks
Left-pad the single-digit week numbers with '0'?
Note that rCTE's are an incredibly expensive tool for generating a date range. This method is far cheaper:
DECLARE
@DateStart DATETIME = '20170101',
@DateEnd DATETIME = '20171010'
;WITH
E1 AS (SELECT n = 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
E2 AS (SELECT n = 0 FROM E1 a, E1 b),
E4 AS (SELECT n = 0 FROM E2 a, E2 b),
_Tally AS (
SELECT TOP(1+DATEDIFF(WEEK,@DateStart,@DateEnd))
TheDate = DATEADD(WEEK,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,@DateStart)
FROM E4)
SELECT
TheDate,
YearMonth = '[' + CAST(YEAR(TheDate) AS VARCHAR(7)) + '_' + RIGHT('0'+CAST(datepart(week, TheDate) AS VARCHAR(2)),2) + ']'
FROM _Tally
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 10, 2017 at 8:19 am
What happens if you change to
ORDER BY dt?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 10, 2017 at 8:23 am
I think that since you are interested in number of week of the date and not the date it self, you can add 7 days in the recursion. Then you can get rid of the group by clause because each value will be returned only once (and another benefit is that the recursion will run less times). Then you can order by the date it self.
WITH sample AS (
SELECT CAST('01/01/2017' AS DATETIME) AS dt
UNION ALL
SELECT DATEADD(dd, 7, dt) FROM sample s
WHERE DATEADD(dd, 7, dt) <= CAST('10/10/2017' AS DATETIME))
select ','+ quotename( concat( datepart(year, convert(date,dt)) , '_', datepart(week, convert(date,dt)) )) FROM sample
order by dt
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 10, 2017 at 8:57 am
ChrisM@Work - Tuesday, October 10, 2017 8:19 AMLeft-pad the single-digit week numbers with '0'?
Thanks. That was the obvious solution I missed 🙁 I've included that and it works.
Thanks for the other solution. I haven't used it for now as I don't understand how it works, I need to teach myself the 'over' functionality first.
Phil Parkin - Tuesday, October 10, 2017 8:19 AMWhat happens if you change to
ORDER BY dt?
It refuses to execute with "
Column "sample.dt" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
"
Adi Cohn-120898 - Tuesday, October 10, 2017 8:23 AMI think that since you are interested in number of week of the date and not the date it self, you can add 7 days in the recursion. Then you can get rid of the group by clause because each value will be returned only once (and another benefit is that the recursion will run less times). Then you can order by the date it self.
WITH sample AS (
SELECT CAST('01/01/2017' AS DATETIME) AS dt
UNION ALL
SELECT DATEADD(dd, 7, dt) FROM sample s
WHERE DATEADD(dd, 7, dt) <= CAST('10/10/2017' AS DATETIME))
select ','+ quotename( concat( datepart(year, convert(date,dt)) , '_', datepart(week, convert(date,dt)) )) FROM sample
order by dtAdi
Thanks I will have a look at this.
All: Thanks for your help.
October 10, 2017 at 9:21 am
You can also try the following, which will use a parameter for the starting date and just use the run date as the last possible date, so you could choose to integrate this into a stored procedure:DECLARE @START_DATE AS date = '20170101';
DECLARE @END_DATE AS date = GETDATE();
WITH SAMPLE_DATA AS (
SELECT TOP (DATEDIFF(week, @START_DATE, @END_DATE) + 1) DATEADD(week, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @START_DATE) AS WEEK_START_DATE
FROM sys.all_objects
)
SELECT '['+ CONVERT(char(4), YEAR(WEEK_START_DATE)) + '_' + RIGHT('0' + CONVERT(varchar(2), DATEPART(week, WEEK_START_DATE)), 2) + ']' AS WEEK_NO,
WEEK_START_DATE,
DATENAME(weekday, WEEK_START_DATE) AS WEEK_START_DAY,
DATEADD(day, 6, WEEK_START_DATE) AS WEEK_END_DATE,
DATENAME(weekday, DATEADD(day, 6, WEEK_START_DATE)) AS WEEK_END_DAY
FROM SAMPLE_DATA;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 10, 2017 at 10:54 am
as1981 - Tuesday, October 10, 2017 8:57 AMThanks. That was the obvious solution I missed 🙁 I've included that and it works.Thanks for the other solution. I haven't used it for now as I don't understand how it works, I need to teach myself the 'over' functionality first.
Phil Parkin - Tuesday, October 10, 2017 8:19 AMWhat happens if you change to
ORDER BY dt?It refuses to execute with "
Column "sample.dt" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
"
Thanks I will have a look at this.
All: Thanks for your help.
My apologies, missed the GROUP BY.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 10, 2017 at 3:40 pm
All,
Thanks for your help.
Andrew
October 12, 2017 at 10:30 am
Instead of inventing your own notation, why don't you use the ISO standard? It's part of ISO–8601. It's a string in the format "yyyyWww-[1-7]" in which the first four digits are the year (yyyy), followed by a W is a marker, then the week within the year from 01 to 52 or 53, a dash as more punctuation, and the digit between one and seven for the weekday number. Instead of writing code for this will find there are several spots on the Internet we can simply download a few hundred years of it.
You can also quickly create a calendar table with the information you need using a spreadsheet. I think your real problem is that you don't understand that, SQL is not COBOL. In COBOL this is exactly what you did had to do back 50 years ago because all dates are handled as strings in that language. But we have temporal datatypes in SQL and want to use them instead
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply