January 8, 2011

Aggregation by rolling week

Filed under: tsql Himanshu @ 5:37 pm

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

Powered by WordPress