CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2012
ariel
ariel

Reputation: 77

Cumulative Calculation

I have table of users with purchase data, join date, purchase date and purchase value.

I want to calculate cumulative purchase value per user per date from join date.

Table:

user    join_date   purchase date   purchase value
--------------------------------------------------
1       9/1/2016    9/7/2016        50
2       1/5/2015    6/9/2016        45
3       9/7/2016    10/8/2016       89

I want to extract the data like:

user    join_date   purchase date   purchase value  WW  
-------------------------------------------------------
1       9/1/2016    Null            0               35  
1       9/1/2016    9/7/2016        50              36  
1       9/1/2016    Null            0               37  
1       9/1/2016    Null            0               38

Thanks

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

In SQL Server 2012+, you would use the ANSI standard way of calculating a cumulative sum.

I'm not sure what your desired results have to do with the given data, but the answer to your written question is:

select p.*, 
       sum(purchase_value) over (partition by user order by purchase_date) as cumulative_value
from purchases p;

Upvotes: 1

Related Questions