March 21, 2014 at 1:37 pm
I have a calendar table that I need to retrieve a list of ISOWeek beginning dates from. I need the list of dates to appear in a row with corresponding week numbers as the column headings. This will get me the data I need.
Create Table #Dates (CalendarDate Date, ISOWeek int)
Insert #Dates
Select date, ISOWeek
From Calendar
Where DayName = 'Monday' AND ISOYear = 2013
Here is the resultset:
CalendarDateISOWeek
12/31/20121
1/7/20132
1/14/20133
1/21/20134
1/28/20135
2/4/20136
2/11/20137
2/18/20138
2/25/20139
3/4/201310
3/11/201311
3/18/201312
3/25/201313
4/1/201314
4/8/201315
4/15/201316
4/22/201317
4/29/201318
5/6/201319
5/13/201320
5/20/201321
5/27/201322
6/3/201323
6/10/201324
6/17/201325
6/24/201326
7/1/201327
7/8/201328
7/15/201329
7/22/201330
7/29/201331
8/5/201332
8/12/201333
8/19/201334
8/26/201335
9/2/201336
9/9/201337
9/16/201338
9/23/201339
9/30/201340
10/7/201341
10/14/201342
10/21/201343
10/28/201344
11/4/201345
11/11/201346
11/18/201347
11/25/201348
12/2/201349
12/9/201350
12/16/201351
12/23/201352
I can transpose this into columns with the ISOWeek as column names with this code:
--Declare some variables for the dynamic SQL string
Declare @SQL1 varchar(8000)
Declare @SQL2 varchar(8000)
Declare @SQL3 varchar(8000)
--Populate the variables
Set @SQL1 = 'Select '
Select @SQL2 = ISNULL(@SQL2+',','') + CHAR(13)
+ 'SUM(CASE WHEN CalendarDate = '''
+ CAST(CalendarDate as varchar(15)) + ''' THEN ISOWeek Else 0 END) AS ['
+ CAST(CalendarDate as varchar(15)) + ']'
From #Dates
Select @SQL3 = CHAR(13) + 'From #Dates'
--Print the dynamic SQL command so that we can examine it
Print @SQL1+@SQL2+@SQL3
--Execute the command
Execute (@SQL1+@SQL2+@SQL3)
Which gives me this:
2012-12-312013-01-072013-01-142013-01-212013-01-282013-02-042013-02-112013-02-182013-02-252013-03-042013-03-112013-03-182013-03-252013-04-012013-04-082013-04-152013-04-222013-04-292013-05-062013-05-132013-05-202013-05-272013-06-032013-06-102013-06-172013-06-242013-07-012013-07-082013-07-152013-07-222013-07-292013-08-052013-08-122013-08-192013-08-262013-09-022013-09-092013-09-162013-09-232013-09-302013-10-072013-10-142013-10-212013-10-282013-11-042013-11-112013-11-182013-11-252013-12-022013-12-092013-12-162013-12-23
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
But I need the ISOWeek numbers as column headings and the dates as a row of data. My code from above won't work because you cant aggregate dates. If I switch the column names I get this error:
Operand type clash: int is incompatible with date
I've been working on this for a couple of days now, I've tried to use Pivot and Unpivot to no avail, and I'm stumped. Does anyone have an idea? I need ONE row in the result set containing all 52 (or 53) calendar dates that represent the beginning date of an ISOWeek for a user selected ISOYear. Thanks in advance for any help.
March 21, 2014 at 1:55 pm
Why do you say you can't use aggregate functions on dates? SUM isn't the only aggregate available.
Is this what you need?
Select @SQL2 = ISNULL(@SQL2+',','') + CHAR(13)
+ 'MAX(CASE WHEN CalendarDate = '''
+ CAST(CalendarDate as varchar(15)) + ''' THEN CalendarDate END) AS ['
+ CAST(ISOWeek as char(2)) + ']'
From #Dates
Or like this which gives the same result but uses the columns differently
Select @SQL2 = ISNULL(@SQL2+',','') + CHAR(13)
+ 'MAX(CASE WHEN ISOWeek = '
+ CAST(ISOWeek as varchar(2)) + ' THEN CalendarDate END) AS ['
+ CAST(ISOWeek as varchar(2)) + ']'
From #Dates
March 21, 2014 at 2:09 pm
Luis, you are correct. MAX didn't work because it always gave me the last week of the year. But MIN works fine if I compare the CalenderDate to GETDATE()!
Thanks a lot. I was in a rut!!:-D
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply