SQL Server: How to get a rolling sum over 3 days for different customers within same table

Question

This is the input table:

Customer_ID  Date       Amount
1            4/11/2014  20
1            4/13/2014  10
1            4/14/2014  30
1            4/18/2014  25
2            5/15/2014  15
2            6/21/2014  25
2            6/22/2014  35
2            6/23/2014  10

There is information pertaining to multiple customers and I want to get a rolling sum across a 3 day window for each customer. The solution should be as below:

Customer_ID  Date       Amount  Rolling_3_Day_Sum
1            4/11/2014  20      20
1            4/13/2014  10      30
1            4/14/2014  30      40
1            4/18/2014  25      25
2            5/15/2014  15      15
2            6/21/2014  25      25
2            6/22/2014  35      60
2            6/23/2014  10      70

The biggest issue is that I don't have transactions for each day because of which the partition by row number doesn't work.

The closest example I found on SO was: SQL Query for 7 Day Rolling Average in SQL Server but even in that case there were transactions made everyday which accomodated the rownumber() based solutions

The rownumber query is as follows:

select customer_id, Date, Amount, 
Rolling_3_day_sum = CASE WHEN ROW_NUMBER() OVER (partition by customer_id ORDER BY Date) > 2
    THEN SUM(Amount) OVER (partition by customer_id ORDER BY Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    END
from #tmp_taml9
order by customer_id

I was wondering if there is way to replace "BETWEEN 2 PRECEDING AND CURRENT ROW" by "BETWEEN [DATE - 2] and [DATE]"

Answer 1

One option would be to use a calendar table (or something similar) to get the complete range of dates and left join your table with that and use the row_number based solution.

Another option that might work (not sure about performance) would be to use an apply query like this:

select customer_id, Date, Amount, coalesce(Rolling_3_day_sum, Amount) Rolling_3_day_sum
from #tmp_taml9 t1 
cross apply (
    select sum(amount) Rolling_3_day_sum 
    from #tmp_taml9 
    where Customer_ID = t1.Customer_ID 
      and datediff(day, date, t1.date) <= 3 
      and t1.Date >= date
) o
order by customer_id;

I suspect performance might not be great though.