felipesch
asked on
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.
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
> 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
Actually that was one of the adjustments :-)
Indeed :)
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