In my current project, we had interesting problem, customer wants to see few data aggregation by rolling week and not year week. Rolling week mean say today is ‘Saturday’, data should aggregate by all week ending ‘Saturday’, but if it was ‘Tuesday’, data should aggregate by by all week ending ‘Tuesday’. When I heard this for first time, I thought this going to end up real big and complex procedure. But to my surprise it’s not:
1: select customerId, bSaleDate as SaleDate, SUM(Amount) as Amount
2: from
3: (
4: select distinct
5: SaleByCustomerAndDate.customerId,
6: SaleSevenDayRange.SaleDate as bSaleDate,
7: SaleByCustomerAndDate.SaleDate as aSaleDate,
8: SaleByCustomerAndDate.Amount as Amount
9: from
10: (
11: select
12: customerId,
13: SaleDate,
14: SUM(Amount) as Amount
15: from
16: Sale
17: group by
18: customerId,
19: SaleDate
20: )
21: as SaleByCustomerAndDate inner join
22: (
23: select
24: customerId,
25: SaleDate,
26: DATEADD(DAY,-6,SaleDate) as LastSevenDay
27: from Sale
28: ) as SaleSevenDayRange on SaleByCustomerAndDate.SaleDate
29: between
30: SaleSevenDayRange.LastSevenDay
31: and
32: SaleSevenDayRange.SaleDate
33: and SaleByCustomerAndDate.customerId = SaleSevenDayRange.customerId
34: ) as c
35: group by
36: customerId,
37: bSaleDate
38: order by customerId, SaleDate
