OLAP Running Sum

[BrianTyler]

The customers pay weekly, usually by small amounts.

I am trying to get details of how much each customer has paid up to the end of each month, effectively a running sum reset at change of customer, with a row being output for each customer/month.

The obvious answer is OLAP, but I can find no real explanations of how to use the functions.

select
cust_no
,month(txn_apply_dt)
,sum(txn_amt)
over (partition by cust_no order by month(txn_apply_dt))
from
custfin;

The above SQL seems to output one row for each year/month but does not give a running sum.

select
cust_no
,month(txn_apply_dt)
,sum(txn_amt)
over (order by cust_no, month(txn_apply_dt))
from
custfin;

This SQL produces a running sum on each row, but does not reset the sum at change of customer.

Does anyone understand OLAP well enough to advise me.

Thanks

Brian

 

[blom0344]

Brian,

Basically the 'partition by' phrase should do the resetting for you. Did you already download the SQL cookbooks by Graeme Birchall?
OLAP functions are explained there to the full extend, better than I could ever explain.....

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[BrianTyler]

Thanks Tiers,

I somehow managed to blunder through to the correct result by creating a temporary table giving monthly totals for each customer, and then using:

partition by cust_no order by month(txmonth)

Graeme's cookbook is good, but I am having trouble understanding some of the OLAP functions. When I have time I will study it in more detail,and work therough the examples.

Brian