Running Totals

Last Post 15 Mar 2004 12:40 PM by prospec. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
skt5000
New Member
New Member

--
11 Mar 2004 08:27 AM
Is there a way to compute running totals in SQL as in an Excel Pivot table. I have data by claim#, Accident year quarter (200303,200304,200401, etc.) and Calendar year quarter.

claim# AYQ CYQ Total
--------- --------- --------- ---------
claim1 200301 200301 5
claim1 200301 200302 15
claim1 200301 200303 25

That I would like to accumulate

claim# AYQ CYQ Total
--------- --------- -- ------- --------
claim1 200301 200301 5
claim1 200301 200302 20
claim1 200301 200303 45

As an added bonus, there can be many instances of each AYQ and CYQ combination, the only stipulation is that AYQ be <= CYQ


Thanks
mwesch
New Member
New Member

--
11 Mar 2004 09:13 AM
What is primary key of table? Do you want running totals calculated by AYQ or by CYQ?

I'm going to assume that table key is Claim# and AYQ, and that you want running totals by CYQ.

-------------------------------------------------------------------------
/*
create table Claim
(
Claim varchar(25),
AYQ char(6),
CYQ char(6),
Total money
)

insert claim select 'claim1','200301','200301', 5
insert claim select 'claim1','200301','200302', 15
insert claim select 'claim1','200301','200303', 25
*/

select a.claim, a.ayq, a.cyq, a.total, sum(b.total) as runningtotal
from claim a
inner join claim b
on a.claim = b.claim
and a.ayq = b.ayq
and a.cyq >= b.cyq
group by a.claim, a.ayq, a.cyq, a.total
skt5000
New Member
New Member

--
11 Mar 2004 09:28 AM
Thanks!
I had a.cyq <= b.cyq and that doesn't work. How does SQL process this code?

ScottPletcher
New Member
New Member

--
11 Mar 2004 01:33 PM
You can also do it this way:



SELECT claim#, AYQ, CYQ, (
SELECT SUM(Total)
FROM claims c2
WHERE c2.claim# = claims.claim#
AND c2.AYQ = claims.AYQ
AND c2.CYQ <= claims.CYQ ) AS [Total]
FROM claims
skt5000
New Member
New Member

--
12 Mar 2004 04:15 AM
This is great. I will experiment with using both. My AYQ is actually a field I calculate from table1 and then join it to the CYQ from table2 and from I there I can start cumulating. I ight be able to create the report with just one query.

Thanks
prospec
New Member
New Member

--
15 Mar 2004 12:40 PM
it may also be worth looking at the "compute" syntax in t-sql. check BOL for more info.
You are not authorized to post a reply.

Acceptable Use Policy