Cummulative

I'm having trouble creating a cummulative column in a table.

I've got the following table, sorted ASC PAID

Claim_Number Paid
123 $21.22
124 $34,87
456 $42.90
478 $67.00
890 $100.10

I want to add a column (in a new table) with the cummulative total paid. It should look like

Claim_Number Paid Cummulative
123 $21.00 $21.00
124 $34.00 $55.00
456 $42.00 $97.00
478 $67.00 $164.00
890 $100.00 $264.00

Thanks for the help.

RayIf you don't have thousands of rows, try using a CURSOR like this (I've assumed your table is named tblCumulative):

DECLARE @.Paid MONEY
DECLARE @.Claim_Number INT
DECLARE @.Total MONEY
DECLARE @.Cumulative MONEY

SET @.Total = 0.0

DECLARE cur_Cumulative CURSOR FOR
SELECT Paid, Claim_Number, Cumulative
FROM tblCumulative
ORDER BY Paid ASC, Claim_Number ASC

OPEN cur_Cumulative
FETCH NEXT FROM cur_Cumulative INTO @.Paid, @.Claim_Number, @.Cumulative
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Total = @.Total + @.Paid
UPDATE tblCumulative
SET Cumulative = @.Total
WHERE Paid = @.Paid and Claim_Number = @.Claim_Number
FETCH NEXT FROM cur_Cumulative INTO @.Paid, @.Claim_Number, @.Cumulative
END

CLOSE cur_Cumulative
DEALLOCATE cur_Cumulative

Note that this falls down if the combination of Paid and Claim_Number is not unique. If this is possible, consider having an IDENTITY column as a primary key, and referencing that new column in the CURSOR and in the UPDATE WHERE clause.|||CURSORS!?!?!?!?!? NOOOOOOOOOO!!! Stomp on them! Crush their little heads! AAArrrggghhh! :mad:

Select YourTable.ClaimNumber,
YourTable.Paid,
Sum(SubTable.Paid) as Cummulative
From YourTable
Inner join YourTable SubTable
on YourTable.Paid >= SubTable.Paid
and YourTable.ClaimNumber >= Subtable.ClaimNumber --In case two claims paid the same...
Group by YourTable.ClaimNumber,
YourTable.Paid
Order by YourTable.Paid,
YourTable.ClaimNumber

Aaahhh...I feel better now. There is one less cursor in the world. :)

...though why you want cumulative sorted by paid amount is beyond me...|||Close but no cigar. Using Blindman's approach, with 2 invoices for the same amount (I added Claim_Number = 1, Paid = 34.87) ...

123 21.2200 21.2200
1 34.8700 34.8700
124 34.8700 90.9600
456 42.9000 133.8600
478 67.0000 200.8600
890 100.0000 300.8600

... but using the hated cursor (yes, I hate them too)

123 21.2200 21.2200
1 34.8700 56.0900
124 34.8700 90.9600
456 42.9000 133.8600
478 67.0000 200.8600
890 100.0000 300.8600

I'm willing to bet Blindman can fix his solution with the next post, so although the cursor solution still works, I'd hold off for a few more hours yet!|||Yes, that was a gotcha, but fortunately I do not smoke cigars.

set nocount on

create table #Invoices (ClaimNumber int, Paid money)

insert into #Invoices (ClaimNumber, Paid) values (123, $21.22)
insert into #Invoices (ClaimNumber, Paid) values (1, $34.87)
insert into #Invoices (ClaimNumber, Paid) values (124, $34.87)
insert into #Invoices (ClaimNumber, Paid) values (456, $42.90)
insert into #Invoices (ClaimNumber, Paid) values (478, $67.00)
insert into #Invoices (ClaimNumber, Paid) values (890, $100.10)

Select #Invoices.ClaimNumber,
#Invoices.Paid,
Sum(SubTable.Paid) as Cummulative
From #Invoices
Inner join #Invoices SubTable
on #Invoices.Paid > SubTable.Paid
or (#Invoices.Paid = SubTable.Paid and #Invoices.ClaimNumber >= Subtable.ClaimNumber)
Group by #Invoices.ClaimNumber,
#Invoices.Paid
Order by #Invoices.Paid,
#Invoices.ClaimNumber

Drop table #Invoices

I still think the requirement is a little odd, as far as including the Paid value in the record ordering.|||Blindman.
Thanks for the help. Why order the paid cummulative is because we do stratified sampling for audits. You sort everything by dollars and then cut into strata. That's basically a crappy way of saying that it enables you to sample claims in a manner that you audit more dollars per claim than you would if you sampled straight random.

Ray|||Manipulating data to give the impression of better performance!? What is business in this country coming to?

"I am shocked, shocked, to find accounting going on here."|||No, not manipulating data for the sake of performance. But perhaps a bit misleading. Stratification is a used on lots of sampling algorithms (stratify by age, income, claim dollars,...). You're basically sorting the population (by age, paid dollars, income,...) and then chopping it into strata to make sure your sample is represented by the right age or dollar or income levels.

The average healthcare claim in the U.S. is about $175. And the average person has about 10 a year. Health insurance companies are required to measure payment accuracy. If they straight random sample they end up with a sample (of say 400 claims) of which 80 to 90% are claims under $200. Stratification means they'll put more high dollar claims in their sample and so they can audit them and make sure they're being paid correctly.|||Why are U.S. corporations always talking about the "average person"? What about those of us who are below average? Nearly 50% of us are, you know, and some of us are significantly below average. We are people too. I'm tired of being marginalized just because, well, just because I'm on the margins.

Hopefully our situation will improve now that we have one of our own in the White House.|||...but fortunately I do not smoke cigars.

THAT is just WRONG...sick and WRONG, I tell ya... SICK and WRONG!!!! ;)