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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Scott PletcherSenior DBACommented:
Actually that was one of the adjustments :-)
Patrick MatthewsCommented:
Indeed :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.