tsql - 查询进入新年的周数的最佳方法是什么？

Question

我正在尝试编写一个查询，该查询将获取本周、前 8 周和后 8 周的每周账单总额。我现在的查询工作正常，但是因为周数将在新的一年中重置，数据从我的 Where 子句中的 between 语句中丢失。有没有更好、更有效的方式来查询这些数据？

查询如下

SET DATEFIRST 7
select BillingDate,
SumOfAmountBilled as BillingTotal 
Into #TempTable
     from MM_Billing_Sum_Table 
     where DATEPART(hour,billingdate) = 21
     and billingdate >= '1/1/2014'
     GROUP BY BillingDate,SumOfAmountBilled
     Order By BillingDate desc

select 
 Distinct 'Week: ' + RIGHT('0' + CAST(datepart (week, billingdate) AS VARCHAR(2)),2) as 'Week',
    --Figure out which year to sort it in
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2010%' then BillingTotal end), 0) '2010',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2011%' then BillingTotal end), 0) '2011',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2012%' then BillingTotal end), 0) '2012',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2013%' then BillingTotal end), 0) '2013',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2014%' then BillingTotal end), 0) '2014',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2015%' then BillingTotal end), 0) '2015',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2016%' then BillingTotal end), 0) '2016',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2017%' then BillingTotal end), 0) '2017',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2018%' then BillingTotal end), 0) '2018',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2019%' then BillingTotal end), 0) '2019',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2020%' then BillingTotal end), 0) '2020',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2021%' then BillingTotal end), 0) '2021',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2022%' then BillingTotal end), 0) '2022',
    isnull(Sum(case when DATEPART(YEAR, billingdate) like '2023%' then BillingTotal end), 0) '2023'
from #TempTable z
where --convert(varchar, (Format(billingdate, 'MM'))) in ((CONVERT(char(2), (DATEADD(month, +1, GETDATE())), 101)), (CONVERT(char(2), getdate(), 101)), (CONVERT(char(2), (DATEADD(month, -1, GETDATE())), 101)), (CONVERT(char(2), (DATEADD(month, -2, GETDATE())), 101)))
datepart(week, billingdate) between datepart(week, ((DATEADD(week, -12, '12/11/2019 12:00:00 AM')))) and datepart(week, ((DATEADD(week, +8, '12/11/2019 12:00:00 AM'))))
and DATEPART(YEAR, billingdate) between datepart(year, dateadd(year, -3, '12/11/2019 12:00:00 AM')) and datepart(year, dateadd(year, +1, '12/11/2019 12:00:00 AM'))
Group By  'Week: ' + RIGHT('0' + CAST(datepart (week, billingdate) AS VARCHAR(2)),2)--, billingdate, BillingTotal
order by  'Week: ' + RIGHT('0' + CAST(datepart (week, billingdate) AS VARCHAR(2)),2)--, billingdate, BillingTotal

--drop table #TempTable

当前结果:



预期结果模型:

Answer 1

如果我正确理解您的请求，它应该可以工作。

只需替换旧查询中的第一个条件

和:

...
...
WHERE
datepart(week, billingdate) IN 
(
select datepart(week, ((DATEADD(week,-8, '12/11/2019 12:00:00 AM'))))  
union
select datepart(week, ((DATEADD(week,-7, '12/11/2019 12:00:00 AM'))))  
union 
select datepart(week, ((DATEADD(week,-6, '12/11/2019 12:00:00 AM')))) 
union
select datepart(week, ((DATEADD(week,-5, '12/11/2019 12:00:00 AM'))))  
union 
select datepart(week, ((DATEADD(week,-4, '12/11/2019 12:00:00 AM')))) 
union
select datepart(week, ((DATEADD(week,-3, '12/11/2019 12:00:00 AM')))) 
union
select datepart(week, ((DATEADD(week,-2, '12/11/2019 12:00:00 AM'))))  
union 
select datepart(week, ((DATEADD(week,-1, '12/11/2019 12:00:00 AM')))) 
union
select datepart(week, ((DATEADD(week, 0, '12/11/2019 12:00:00 AM')))) 
union                                 
select datepart(week, ((DATEADD(week, 1, '12/11/2019 12:00:00 AM')))) 
union                                 
select datepart(week, ((DATEADD(week, 2, '12/11/2019 12:00:00 AM')))) 
union                                 
select datepart(week, ((DATEADD(week, 3, '12/11/2019 12:00:00 AM')))) 
union                                 
select datepart(week, ((DATEADD(week, 4, '12/11/2019 12:00:00 AM'))))  
union                                 
select datepart(week, ((DATEADD(week, 5, '12/11/2019 12:00:00 AM')))) 
union                                 
select datepart(week, ((DATEADD(week, 6, '12/11/2019 12:00:00 AM')))) 
union
select datepart(week, ((DATEADD(week, 7, '12/11/2019 12:00:00 AM')))) 
union 
select datepart(week, ((DATEADD(week, 8, '12/11/2019 12:00:00 AM')))) 
) 
...
...