SQL Cumulative

I have a query that return this:

DATA                        CASOS_CRIADOS    CASOS_ENCERRADOS       A-B  
01/03/2006      53                    42                          11
02/03/2006      100                     55                          45
03/03/2006      107                    91                          16
04/03/2006      89                    17                          72
...

What can I create a collum named "A-B-CUMULATIVE", thats represents the cumulative values from collum CRIADOS_MINUS_ENCERRADOS? The result is this:

DATA                        CASOS_CRIADOS    CASOS_ENCERRADOS       A-B     A-B-CUMULATIVE
01/03/2006      53                    42                          11      11          
02/03/2006      100                     55                          45      65                   <-- THE LAST A-B + ACTUAL
03/03/2006      107                    91                          16      81                   <-- THE LAST A-B + ACTUAL
04/03/2006      89                    17                          72      153                 <-- THE LAST A-B + ACTUAL
...

Thanks in advance,

Felipe.

felipeschAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
I think matthewspatrick's query needs some adjustments:


SELECT DATA, CASOS_CRIADOS, CASOS_ENCERRADOS,
    (CASOS_CRIADOS - CASOS_ENCERRADOS) AS [A-B],
    (SELECT SUM(CASOS_CRIADOS - CASOS_ENCERRADOS)
    FROM YourTable tt WHERE tt.DATA <= t.DATA) AS [A-B-Cumulative]
FROM YourTable t
ORDER BY DATA
0
 
Patrick MatthewsCommented:
Hi felipesch,

Something like this should work:

SELECT t.DATA, (t.CASOS_CRIADOS + t.CASOS_ENCERRADOS) AS TheSum,
    t.CASOS_CRIADOS, t.CASOS_ENCERRADOS, (SELECT SUM(tt.CASOS_ENCERRADOS)
    FROM YourTable tt WHERE tt.DATA = t.DATA) AS RunningTot
FROM YourTable t
ORDER BY t.DATA

Regards,

Patrick
0
 
pootle_flumpCommented:
I have no idea what actual is - the numbers don't seem to m,ake much sense - but you need a cursor to do this (or some other looping alternative). You will also need to explicitly enforce your order with an ORDER BY statement as there is no such thing as Last in an RDBMS.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Patrick MatthewsCommented:
felipesch,

Sorry, had a typo:

SELECT t.DATA, (t.CASOS_CRIADOS + t.CASOS_ENCERRADOS) AS TheSum,
    t.CASOS_CRIADOS, t.CASOS_ENCERRADOS, (SELECT SUM(tt.CASOS_ENCERRADOS)
    FROM YourTable tt WHERE tt.DATA <= t.DATA) AS RunningTot
FROM YourTable t
ORDER BY t.DATA


Regards,

Patrick
0
 
Patrick MatthewsCommented:
Scott,

> I think matthewspatrick's query needs some adjustments:

Almost certainly.  I had trouble following what the various columns were; I focused my energies on getting
the subquery to run the cumulative totals :)

Patrick
0
 
Scott PletcherSenior DBACommented:
Actually that was one of the adjustments :-)
0
 
Patrick MatthewsCommented:
Indeed :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.