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:
CalendarDate ISOWeek
12/31/2012 1
1/7/2013 2
1/14/2013 3
1/21/2013 4
1/28/2013 5
2/4/2013 6
2/11/2013 7
2/18/2013 8
2/25/2013 9
3/4/2013 10
3/11/2013 11
3/18/2013 12
3/25/2013 13
4/1/2013 14
4/8/2013 15
4/15/2013 16
4/22/2013 17
4/29/2013 18
5/6/2013 19
5/13/2013 20
5/20/2013 21
5/27/2013 22
6/3/2013 23
6/10/2013 24
6/17/2013 25
6/24/2013 26
7/1/2013 27
7/8/2013 28
7/15/2013 29
7/22/2013 30
7/29/2013 31
8/5/2013 32
8/12/2013 33
8/19/2013 34
8/26/2013 35
9/2/2013 36
9/9/2013 37
9/16/2013 38
9/23/2013 39
9/30/2013 40
10/7/2013 41
10/14/2013 42
10/21/2013 43
10/28/2013 44
11/4/2013 45
11/11/2013 46
11/18/2013 47
11/25/2013 48
12/2/2013 49
12/9/2013 50
12/16/2013 51
12/23/2013 52
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-31 2013-01-07 2013-01-14 2013-01-21 2013-01-28 2013-02-04 2013-02-11 2013-02-18 2013-02-25 2013-03-04 2013-03-11 2013-03-18 2013-03-25 2013-04-01 2013-04-08 2013-04-15 2013-04-22 2013-04-29 2013-05-06 2013-05-13 2013-05-20 2013-05-27 2013-06-03 2013-06-10 2013-06-17 2013-06-24 2013-07-01 2013-07-08 2013-07-15 2013-07-22 2013-07-29 2013-08-05 2013-08-12 2013-08-19 2013-08-26 2013-09-02 2013-09-09 2013-09-16 2013-09-23 2013-09-30 2013-10-07 2013-10-14 2013-10-21 2013-10-28 2013-11-04 2013-11-11 2013-11-18 2013-11-25 2013-12-02 2013-12-09 2013-12-16 2013-12-23
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
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