SQL Server 2008 Dynamic Heading (Weekly) for Pivot Query

Question

I want to create a dynamic header that shows how many weeks are between the starting date and the ending date. I already did add the week numbers and the year. Now I want to the DATENAME between those weeks and year and I need to fix the bug.

Here's what I've done so far:

DECLARE @Query AS VARCHAR(MAX)
DECLARE @Weeks INT
DECLARE @DayNum INT
DECLARE @tmp TABLE(WeekNum VARCHAR(MAX))
DECLARE @Headers VARCHAR(MAX)
DECLARE @DayNumbers INT = 1


DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME

SET @FromDate = '01/01/2016'
SET @ToDate = '03/01/2016'

SET @DayNum = 1 

DECLARE @Years INT = YEAR(@FromDate)
DECLARE @DateRange INT = DATEDIFF(Week,@FromDate,'12/31/'+CAST(YEAR(@FromDate) AS VARCHAR))
SET @Weeks = DATEDIFF(Week, @FromDate, @ToDate)

WHILE 1 = 1
BEGIN
    INSERT INTO @tmp(WeekNum) 
    VALUES ('Week ' + CAST(@DayNumbers AS VARCHAR(MAX)) + ' - ' + CAST(@Years AS VARCHAR)) 

    IF @DayNum <= @Weeks
    BEGIN
        SET @DayNum = @DayNum + 1
        SET @DayNumbers = @DayNumbers + 1

        IF @DateRange = @DayNumbers 
        BEGIN
            SET @Years = @Years + 1
            SET @DateRange = DATEDIFF(WEEK,'01/01/' + CAST(@Years AS VARCHAR),'12/31/' + CAST(@Years AS VARCHAR))
            SET @DayNumbers = 1
        END 
    END
    ELSE 
        BREAK    
END

SELECT @Headers = ISNULL(@Headers + ',','') + QUOTENAME(t.WeekNum)
FROM @tmp t


SELECT * 
FROm @tmp 

Here is the result of weeks between Jan 1, 2016 and March 1, 2016

Let's try different dates Jan 1, 2016 and Dec 31,2016

Here is the other issue.

It goes up all the way to 2017 even though it is only until Dec 2016

Answer 1

OK, this one will start the week counter as 1 starting at whatever date you supply.

I added a little wrinkle. I gave you DateR1 and DateR2 to facilitate the aggregation of you data between the two dates.

Declare @DateStart Date = '2016-01-15'
Declare @DateEnd   Date = '2016-12-31'

;with cteDate As (
    Select WkCntr = 1,DateR1 = @DateStart, DateR2 = DateAdd(DD,6,@DateStart)
    Union All 
    Select WkCntr = 1+df.WkCntr,DateR1= DateAdd(DD, 7, df.DateR1), DateR2 = DateAdd(DD,7,df.DateR2)
     From cteDate DF 
     Where DF.DateR1 <= @DateEnd
)
Select *,WeekNum = concat('Week ',WkCntr)+concat(' - ',Year(DateR1)) 
 From cteDate
 Where DateR1<=@DateEnd
 option (maxrecursion 32767)

Returns

WkCntr  DateR1      DateR2      WeekNum
1       2016-01-15  2016-01-21  Week 1 - 2016
2       2016-01-22  2016-01-28  Week 2 - 2016
3       2016-01-29  2016-02-04  Week 3 - 2016
4       2016-02-05  2016-02-11  Week 4 - 2016
5       2016-02-12  2016-02-18  Week 5 - 2016
...
49      2016-12-16  2016-12-22  Week 49 - 2016
50      2016-12-23  2016-12-29  Week 50 - 2016
51      2016-12-30  2017-01-05  Week 51 - 2016

Now, here is an example how to aggregate your data. Let's assume

-- Let's create a dummy SALES Table
Declare @SalesTable table (SalesDate date, Sales money)
Insert Into @SalesTable values
('2016-01-16',25),
('2016-01-22',10),
('2016-02-05',75),
('2016-02-22',125)

--- The Previous displayed cte
Declare @DateStart Date = '2016-01-15'
Declare @DateEnd   Date = '2016-12-31'

;with cteDate As (
    Select WkCntr = 1,DateR1 = @DateStart, DateR2 = DateAdd(DD,6,@DateStart)
    Union All 
    Select WkCntr = 1+df.WkCntr,DateR1= DateAdd(DD, 7, df.DateR1), DateR2 = DateAdd(DD,7,df.DateR2)
     From cteDate DF 
     Where DF.DateR1 <= @DateEnd
),
cteDateRange as (
    Select *,WeekNum = concat('Week ',WkCntr)+concat(' - ',Year(DateR1)) 
     From cteDate
     Where DateR1<=@DateEnd
)
Select A.DateR1
      ,A.DateR1
      ,A.WeekNum
      ,Sales=isnull(sum(Sales),0)
 From cteDateRange A
 Left Join @SalesTable B on (B.SalesDate between DateR1 and DateR2)
 Group By DateR1,DateR2,WeekNum
 Order By 1

Returns

DateR1      DateR1      WeekNum         Sales
2016-01-15  2016-01-15  Week 1 - 2016   25.00
2016-01-22  2016-01-22  Week 2 - 2016   10.00
2016-01-29  2016-01-29  Week 3 - 2016   0.00
2016-02-05  2016-02-05  Week 4 - 2016   75.00
2016-02-12  2016-02-12  Week 5 - 2016   0.00
2016-02-19  2016-02-19  Week 6 - 2016   125.00
2016-02-26  2016-02-26  Week 7 - 2016   0.00
2016-03-04  2016-03-04  Week 8 - 2016   0.00

Answer 2

As requested, this version will start the week counter as of the first Monday of the year..

Declare @DateStart Date = '2016-01-01'
Declare @DateEnd   Date = '2016-12-31'


Declare @FirstMonday Date,@yr int = Year(@DateStart)
Set @FirstMonday = DateAdd(DD,1,case when datepart(weekday,dateadd(year,@yr-1900,0))=1 then dateadd(year,@yr-1900,1) else dateadd(dd,8-(datepart(weekday,dateadd(year,@yr-1900,0))),dateadd(year,@yr-1900,1)) end)

;with cteDate As (
    Select WkCntr = 1,DateFrom = @FirstMonday 
    Union All 
    Select WkCntr = 1+df.WkCntr,DateFrom= DateAdd(DD, 7, df.dateFrom) 
     From cteDate DF 
     Where DF.DateFrom <= @DateEnd
)
Select *,WeekNum = concat('Week ',WkCntr)+concat(' - ',Year(DateFrom)) 
 From cteDate
 Where DateFrom<=@DateEnd
 option (maxrecursion 32767)



WkCntr  DateFrom    WeekNum
1       2016-01-05  Week 1 - 2016
2       2016-01-12  Week 2 - 2016
3       2016-01-19  Week 3 - 2016
...
51      2016-12-20  Week 51 - 2016
52      2016-12-27  Week 52 - 2016

Answer 3

Declare @DateStart Date = '2016-01-01'
Declare @DateEnd   Date = '2016-12-31'

;with cteDate As (
    Select DateFrom = @DateStart 
    Union All 
    Select DateFrom= DateAdd(DD, 7, df.dateFrom) 
     From cteDate DF 
     Where DF.DateFrom <= @DateEnd
)
Select *,WeekNum = concat('Week ',DatePart(WEEK,DateFrom))+concat(' - ',Year(DateFrom)) 
 From cteDate
 Where DateFrom<=@DateEnd
 option (maxrecursion 32767)

Note See what happens when you remove Where DateFrom<=@DateEnd from the final select

Returns

DateFrom    WeekNum
2016-01-01  Week 1 - 2016
2016-01-08  Week 2 - 2016
2016-01-15  Week 3 - 2016
2016-01-22  Week 4 - 2016
2016-01-29  Week 5 - 2016
2016-02-05  Week 6 - 2016
2016-02-12  Week 7 - 2016
2016-02-19  Week 8 - 2016
2016-02-26  Week 9 - 2016
2016-03-04  Week 10 - 2016
2016-03-11  Week 11 - 2016
2016-03-18  Week 12 - 2016
2016-03-25  Week 13 - 2016
2016-04-01  Week 14 - 2016
2016-04-08  Week 15 - 2016
2016-04-15  Week 16 - 2016
2016-04-22  Week 17 - 2016
2016-04-29  Week 18 - 2016
2016-05-06  Week 19 - 2016
2016-05-13  Week 20 - 2016
2016-05-20  Week 21 - 2016
2016-05-27  Week 22 - 2016
2016-06-03  Week 23 - 2016
2016-06-10  Week 24 - 2016
2016-06-17  Week 25 - 2016
2016-06-24  Week 26 - 2016
2016-07-01  Week 27 - 2016
2016-07-08  Week 28 - 2016
2016-07-15  Week 29 - 2016
2016-07-22  Week 30 - 2016
2016-07-29  Week 31 - 2016
2016-08-05  Week 32 - 2016
2016-08-12  Week 33 - 2016
2016-08-19  Week 34 - 2016
2016-08-26  Week 35 - 2016
2016-09-02  Week 36 - 2016
2016-09-09  Week 37 - 2016
2016-09-16  Week 38 - 2016
2016-09-23  Week 39 - 2016
2016-09-30  Week 40 - 2016
2016-10-07  Week 41 - 2016
2016-10-14  Week 42 - 2016
2016-10-21  Week 43 - 2016
2016-10-28  Week 44 - 2016
2016-11-04  Week 45 - 2016
2016-11-11  Week 46 - 2016
2016-11-18  Week 47 - 2016
2016-11-25  Week 48 - 2016
2016-12-02  Week 49 - 2016
2016-12-09  Week 50 - 2016
2016-12-16  Week 51 - 2016
2016-12-23  Week 52 - 2016
2016-12-30  Week 53 - 2016