0

I have a date field where I am trying to group by the next Monday date (as reporting week). For Example, Monday 03/02 - Sunday 03/08 would all get grouped to Monday 03/09.

Here is the query I'm using so far, it works, but not for Mondays, it's still tagging Monday 03/02 as 03/02 reporting week, instead of 03/09.

SELECT DATEADD(day, DATEDIFF(day,0, paymentdate-1) /7*7, 0)+7 as ReportingWeek, Market, Bank, sum(amtusd) AmountUSD
from Payments
where year(paymentdate)=2020
and market in ('CAD', 'USD')
group by DATEADD(day, DATEDIFF(day,0, paymentdate-1) /7*7, 0)+7, bank, market
order by reportingweek, market, bank asc
ksaint
  • 1
  • 1

1 Answers1

1

Group by DATEPART(week, paymentdate) and whatever else (bank, market)

Display the value of that number + 1, converted to a date: Get dates from a week number in T-SQL

Remember to SET DATEFIRST to set the first day of the week.

tpdi
  • 34,554
  • 11
  • 80
  • 120