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:

select customerId, bSaleDate as SaleDate, SUM(Amount) as Amount
from
(
    select distinct
        SaleByCustomerAndDate.customerId,
        SaleSevenDayRange.SaleDate as bSaleDate,
        SaleByCustomerAndDate.SaleDate as aSaleDate,
        SaleByCustomerAndDate.Amount as Amount
    from
    (
        select
            customerId,
            SaleDate,
            SUM(Amount) as Amount
        from
            Sale
        group by
            customerId,
            SaleDate
    )
    as SaleByCustomerAndDate inner join
    (
        select
            customerId,
            SaleDate,
            DATEADD(DAY,-6,SaleDate) as LastSevenDay
        from Sale
    ) as SaleSevenDayRange on SaleByCustomerAndDate.SaleDate
        between
            SaleSevenDayRange.LastSevenDay
            and
            SaleSevenDayRange.SaleDate
    and SaleByCustomerAndDate.customerId = SaleSevenDayRange.customerId
) as c
group by
    customerId,
    bSaleDate
order by customerId, SaleDate