What is the best way to query the week number going into a new year?

Question

I am trying to write a query that will get the weekly billing totals for the current week, 8 weeks before, and 8 weeks after. The query I have now works fine, however because the Week Number will be resetting with the new year, the data is falling out of the Between statement in my Where clause. Is there a better, more efficient way to query this data?

Query is below

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

Current Result:

Expected Result Mockup:

Sample data, desired results, a simplified query, and database tag would all help this question. — Gordon Linoff, Dec 12 '19 at 17:21
Updated, although I don't know if I can simplify the query anymore. — user1457104, Dec 12 '19 at 18:00

score 2 · Accepted Answer · answered Dec 12 '19 at 21:34

If I understand your request correctly, it should work.

Just replace your first condition in your old query

with:

...
...
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')))) 
) 
...
...

This gave me what I was looking for, thank you so much! – user1457104 Dec 13 '19 at 15:28 — user1457104, Dec 13 '19 at 15:28

What is the best way to query the week number going into a new year?

1 Answers1