calculating cumulative totals

[mjd3000]

I have the following SQL and I want to add a column for the Evidence field showing cumulative totals for each grouping of programme/programme contract/output for each month. Can anybody help?



select targ.new_programmemoidname as 'Programme', targ.new_programcontracouttidname as 'Programme Contract',
targ.new_monthlyoutputsidname as 'Output',
targ.new_monthnumber as 'Month', targ.new_numberofoutputs as 'Outputs',
count(act.new_outputevmonthnumber) as 'Evidence'
from FilteredNew_monthlyoutput targ
left join FilteredNew_evidence act
on targ.new_monthlyprofileoutid = act.new_claimperiodid
and targ.new_monthlyoutputsid = act.new_evidenceforoutputid
group by targ.new_programmemoidname, targ.new_programcontracouttidname, targ.new_monthlyoutputsidname,
targ.new_monthnumber, targ.new_numberofoutputs
order by targ.new_programmemoidname, targ.new_programcontracouttidname, targ.new_monthlyoutputsidname,
targ.new_monthnumber

 

[markros]

Are you sure you want to do this in T-SQL and not in the report, where it would be much simpler?

If yes, then it's Running total problem, which is fairly complex.

Check

http://www.sqlteam.com/article/calculating-running-totals

 

[sqlmann]

Can you please explan with some example ?

 

[mjd3000]

Hi,

The difference between what I am trying to do and the typical cumulative total field is that the field I want cumulative totals for is a count of total records in a table related to a particular grouping of programme/programme contract/output/month.

 

[markros]

I understand and it makes this problem even more trickier.