SQL Server 2008数据透视查询的动态标题(每周)

Question

我想要创建一个动态的标题，它显示从开始日期到结束日期之间有多少周。我已经把周数和年份加起来了。现在我想在这几周和一年之间使用DATENAME，我需要修复这个bug。

以下是我迄今所做的工作：

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 

这是2016年1月1日至2016年3月1日和3月1日之间几周的结果。

​

​

让我们试试不同的日期，，2016年1月1日，和12月31日，2016年

这是另一个问题。

​

​

一直持续到2017年，尽管它只持续到2016年12月。

Answer 1

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)

注意，当您从最后的选择中删除Where DateFrom<=@DateEnd时，会发生什么？

返回

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

Answer 2

根据要求，这个版本将从一年中的第一个星期一开始一周计数器。

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

好的，这一周计数器将作为1开始，从你提供的任何日期开始。

我加了点皱纹。我给了您DateR1和DateR2，以方便您在这两个日期之间进行数据聚合。

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)

返回

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

下面是一个如何聚合数据的示例。让我们假设

-- 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

返回

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