T-SQL: Cumulative SUM

  • Hello:

    Below is my T-SQL query. Also, attached are its results.

    My focus, primarily, is on the YRACCUMDEP field. I need for this field to be a "cumulative sum" of the DEPEXP4YR field. For each YR field, YRACCUMDEP needs to sum its amount for DEPEXP4YR plus the amount of DEPEXP4YR from previous YR's.

    For example, where YR = "2017", YRACCUMDEP needs to display the sum of 142.86, 130.96, and 166.66.

    I don't know that I want this constructed as a CTE. I say that because, once I get my YRACCUMDEP field to where it suits my needs, I'm going to want the YRNBV field to subtract the COSTBASIS field and the YRACCUMDEP field for each YR.

    Thanks! Please let me know, if there are any questions.

    Much appreciated!

    John

    SELECT DISTINCT TWO.dbo.FA00100.ASSETID AS ASSETID,

    TWO.dbo.FA00100.SHRTNAME AS SHORTNAME,

    TWO.dbo.FA00200.PLINSERVDATE AS PLACEINSERVICEDATE, TWO.dbo.FA00200.ORIGINALLIFEYEARS AS ORIGINALLIFE,

    TWO.dbo.FA00200.COSTBASIS AS COSTBASIS, TWO.dbo.FA00902.FAYEAR AS YR,

    SUM(TWO.dbo.FA00902.AMOUNT) OVER (PARTITION BY TWO.dbo.FA00902.ASSETINDEX,

    TWO.dbo.FA00902.FAYEAR, TWO.dbo.FA00902.BOOKINDX) AS DEPEXP4YR,

    SUM(TWO.dbo.FA00902.AMOUNT) OVER (PARTITION BY TWO.dbo.FA00902.ASSETINDEX,

    TWO.dbo.FA00902.BOOKINDX) AS YRACCUMDEP,

    TWO.dbo.FA00200.COSTBASIS - (SUM(TWO.dbo.FA00902.AMOUNT) OVER (PARTITION BY TWO.dbo.FA00902.ASSETINDEX,

    TWO.dbo.FA00902.BOOKINDX)) as YRNBV,

    TWO.dbo.GL00105.ACTNUMST as DEPEXPACCOUNT,

    CASE WHEN TWO.dbo.FA00200.DEPRECIATIONMETHOD = '1'

    THEN 'Straight-Line Original Life' WHEN TWO.dbo.FA00200.DEPRECIATIONMETHOD = '2'

    THEN 'Straight-Line Remaining Life' ELSE '' END as METHOD,

    TWO.dbo.FA40200.BOOKID AS BOOK, 'Fabrikam, Inc.' as COMPANY

    FROM TWO.dbo.FA00902

    INNER JOIN TWO.dbo.FA00100 ON TWO.dbo.FA00902.ASSETINDEX = TWO.dbo.FA00100.ASSETINDEX

    INNER JOIN TWO.dbo.FA00200 ON TWO.dbo.FA00902.ASSETINDEX = TWO.dbo.FA00200.ASSETINDEX

    AND TWO.dbo.FA00902.BOOKINDX = TWO.dbo.FA00200.BOOKINDX

    INNER JOIN TWO.dbo.FA40200 ON TWO.dbo.FA00200.BOOKINDX = TWO.dbo.FA40200.BOOKINDX

    INNER JOIN TWO.dbo.GL00105 ON TWO.dbo.FA00902.GLINTACCTINDX = TWO.dbo.GL00105.ACTINDX

    WHERE TWO.dbo.FA00902.SOURCDOC LIKE 'FADEP%' AND TWO.dbo.FA00902.TRANSACCTTYPE = '2'

    --REMOVE THE FOLLOWING CLAUSE PRIOR TO "GO-LIVE":

    and TWO.dbo.FA40200.BOOKID = 'INTERNAL' --and TWO.dbo.FA00902.FAYEAR = '2017'

  • The following article explains a way of doing this. Read it carefully and ask any questions that you have.

    http://www.sqlservercentral.com/articles/T-SQL/68467/

    For better answers, read the article linked in my signature.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • That article offers very little, in terms of anything along the lines of examples or knowledge transfer.

    I await another response.

    Thank you.

    John

  • John Roy Ellis (7/17/2014)


    That article offers very little, in terms of anything along the lines of examples or knowledge transfer.

    I await another response.

    Thank you.

    John

    You seem like a fast reader. It confuses me how you could read the whole article and couldn't find examples on the different methods that explain the advantages and disadvantages. I got some good knowledge from it, so I'm not sure what are you looking for.

    And I'll repeat (in a different way), if you want better answers, you'll need to post DDL and sample data (posted as insert into statements) to help the ones here to avoid spending time on preparing the scenario to test the code.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • John Roy Ellis (7/17/2014)


    That article offers very little, in terms of anything along the lines of examples or knowledge transfer.

    I await another response.

    Thank you.

    John

    Here's another response: read the article then comment, not the other way around. It contains virtually everything you need to know about performing running totals in TSQL and is littered with examples.

    Back to your conundrum. You wish to perform a running total over a column of your output. Since the column DEPEXP4YR is calculated, you will probably need to query your current result set - which means using either a CTE or a #temp table. Without sample data it's very difficult to tell. We can't run TSQL against pictures.

    Why have you mixed DISTINCT with an aggregate? Can you reliably predict correct results in your aggregates if you are removing rows from the output? The sort required by DISTINCT can make it an expensive operation, don't use it unless you need to and you know what it's doing.

    Here's your query reformatted for readability:

    SELECT DISTINCT

    b.ASSETID,

    SHORTNAME= b.SHRTNAME,

    PLACEINSERVICEDATE = c.PLINSERVDATE,

    ORIGINALLIFE= c.ORIGINALLIFEYEARS,

    c.COSTBASIS,

    YR= a.FAYEAR,

    DEPEXP4YR= SUM(a.AMOUNT) OVER (PARTITION BY a.ASSETINDEX,a.FAYEAR, a.BOOKINDX),

    YRACCUMDEP= SUM(a.AMOUNT) OVER (PARTITION BY a.ASSETINDEX,a.BOOKINDX),

    YRNBV= c.COSTBASIS - (SUM(a.AMOUNT) OVER (PARTITION BY a.ASSETINDEX,a.BOOKINDX)),

    DEPEXPACCOUNT= e.ACTNUMST,

    METHOD= CASE c.DEPRECIATIONMETHOD

    WHEN '1' THEN 'Straight-Line Original Life'

    WHEN '2' THEN 'Straight-Line Remaining Life'

    ELSE '' END,

    BOOK= d.BOOKID,

    COMPANY= 'Fabrikam, Inc.'

    FROM TWO.dbo.FA00902 a

    INNER JOIN TWO.dbo.FA00100 b

    ON a.ASSETINDEX = b.ASSETINDEX

    INNER JOIN TWO.dbo.FA00200 c

    ON a.ASSETINDEX = c.ASSETINDEX

    AND a.BOOKINDX = c.BOOKINDX

    INNER JOIN TWO.dbo.FA40200 d

    ON c.BOOKINDX = d.BOOKINDX

    INNER JOIN TWO.dbo.GL00105 e

    ON a.GLINTACCTINDX = e.ACTINDX

    WHERE a.SOURCDOC LIKE 'FADEP%'

    AND a.TRANSACCTTYPE = '2'

    --REMOVE THE FOLLOWING CLAUSE PRIOR TO "GO-LIVE":

    and d.BOOKID = 'INTERNAL' --and a.FAYEAR = '2017'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • There are multiple ways you could do this:

    1. CTE like the suggestion above.

    2. Outer Apply function

    3. Using an correlated subselect with an reference to the left table in your where clause.

    4. In 2012 you could use the rows unbounded preceding function.

    The method you would use all depends on your data layer / index usage. The outer apply function could be really handy for SQL 2008 but should be only used when the date columns + columns in the where clause are indexed (same for the subselect).

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply