Results 1 to 10 of 10

Thread: Cummulative

  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: 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.

    Ray

  2. #2
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112

    Lightbulb

    If 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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    CURSORS!?!?!?!?!? NOOOOOOOOOO!!! Stomp on them! Crush their little heads! AAArrrggghhh!

    Code:
    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...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    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!

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, that was a gotcha, but fortunately I do not smoke cigars.

    Code:
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2004
    Posts
    57
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jun 2004
    Posts
    57
    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.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by blindman
    ...but fortunately I do not smoke cigars.
    THAT is just WRONG...sick and WRONG, I tell ya... SICK and WRONG!!!!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •