?
Solved

SQL Cumulative

Posted on 2006-04-06
7
Medium Priority
?
641 Views
Last Modified: 2008-02-01
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.

0
Comment
Question by:felipesch
  • 4
  • 2
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16392178
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
 
LVL 11

Expert Comment

by:pootle_flump
ID: 16392181
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16392210
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 16392573
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16392676
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16392752
Actually that was one of the adjustments :-)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16392814
Indeed :)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question