Calculate Year-Total, Period-Total and Previous-Year-Total and Previous-Period-Total in a performante way

I have a table with sales. The table contains the CustomerNo, Date and Amount of the sale. Now, I have to calculate the following values - for example with the period 01/01/2019 and 03/31/2019

Total for 01/01/2019 to 03/31/2019 (=period total)
Total for 01/01/2018 to 03/31/2018 (=previous year period total)
Total for 01/01/2019 to 12/31/2019 (=year total)
Total for 01/01/2018 to 12/31/2018 (=previous year total)

all this values grouped by CustomerNo.

How can I do this on a performante way? Can I use Window Functions for this case?

Solution

Unfortunately, window functions cannot be employed in this case, because you cannot move within partitions using dates i.e. sum all sales for a customer for today minus one month. I suppose that it's impossible to have a transaction for each customer each day (then window functions could be used). I would start with something like this:

select 
     CustomerNo
    ,sum(case when [Date] >= '2019-01-01' and [Date] <= '2019-03-31' then Amount else 0 end ) as PeriodTotal
    ,sum(case when [Date] >= '2018-01-01' and [Date] <= '2018-03-31' then Amount else 0 end ) as PreviousYearPeriodTotal
    ,sum(case when [Date] >= '2019-01-01' and [Date] <= '2019-12-32' then Amount else 0 end ) as YearTotal
    ,sum(case when [Date] >= '2018-01-01' and [Date] <= '2018-12-32' then Amount else 0 end ) as PreviousYearTotal
from sales
group by CustomerNo