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