Solved

T-SQL:  Cumulative SUM

Posted on 2014-07-17
10
588 Views
Last Modified: 2014-07-19
Hello:

Attached 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!

TBSupport
Need-Cumulative-Depreciation.docx
QUERY.docx
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 1

Author Comment

by:TBSupport
ID: 40203114
Anyone?  Anyone?  :)

TBSupport
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40203467
outputTo accumulate a calculated column into another column can be achieved by "nesting" (there is no need for a CTE)

However please also note you should solve the issue of DISTINCT before you start summing anything. Not only is it wasteful to be calculating rows that are eventually dispensed with via "select distinct" it is also potentially wrong (can produce the wrong figures).

If you run your existing query without DISTINCT what happens?
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40204291
Hi PortletPaul!

When I remove DISTINCT, I get lots of duplicated rows.  For example, my query is returning 56 rows.  Upon removing DISTINCT, I receive 334 rows--many of them duplicates.

TBSupport
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40204310
All of them are "unwanted repetition" (duplicated records are a different thing altogether, here we are dealing with a "resultset" not actual records).

This is produced by one or more of the joins you are using that multiply the rows.

You have to find which of those joins is doing the multiplication and use distinct or group by on that data BEFORE you join. Otherwise your arithmetic could be wrong.

"Select distinct" on the outer query is not the right way out of this issue.


http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_12282-Select-Distinct-is-returning-duplicates.html
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40204348
OK.  I'll give those a try.

In the meantime, can you also send me a link please for the "nesting" capability that you mentioned earlier?  That way, I can "kill two birds with one stone".

Thanks!

TBSupport
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40204469
"nesting" is simple

select
 *
, sum(summed_amount) over(....)
from (
           select
             *
            , sum(amount) as summed_amount
            , row_number() over(partition by x,y,z order by a,b DESC) as rn
           from x
           where y = z
           ) as nested
where rn = 1


For this situation, you want to "nest" the query in your question inside another, that will allow reference to the outcome of sum() over() or row_number() etc by their aliases

I have seen you do this before now, perhaps you use a different word to describe it.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40205330
Hi:

Below is what I just tried.  With this, I get the error "Subquery returned more than 1 value. This is not permitted when the subquery follows..."

Any ideas?

TBSupport

SELECT ASSETID, COSTBASIS, DEPEXP4YR, 
(SELECT SUM(DEPEXP4YR)
                       FROM TWO.dbo.FA00902 b
                       WHERE test.YR <= b.FAYEAR) as YRACCUMDEP,
YRNBV FROM
(
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'
)
as test
GROUP BY ASSETID, COSTBASIS, DEPEXP4YR, YR, YRACCUMDEP, YRNBV

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40205822
PLEASE use code blocks. I edited the above to do this.
------------------------------------------------------------------------

When attempting to eradicate unwanted repetition you need to look deeper. Adding another layer on top isn't going to solve the initial problem.

In the following there are 3 source records each with a value of 100. So the grand total is 300. Only the last of these 4 queries gets the correct figures, which it does by solving the repetition before aggregating):
**MS SQL Server 2008 Schema Setup**:

    
    
    CREATE TABLE SourceData	
    	([ID] int, [AssocNum] int, [Value] int)
    ;
    	
    INSERT INTO SourceData	
    	([ID], [AssocNum], [Value])
    VALUES
    	(1, 2015, 100),
    	(2, 2016, 100),
    	(5, 2017, 100)
    ;
    
    
    
    CREATE TABLE AssoociatedData	
    	([ID] int, [Num] int)
    ;
    	
    INSERT INTO AssoociatedData	
    	([ID], [Num])
    VALUES
    	(1, 2015),
    	(2, 2015),
    	(3, 2016),
    	(4, 2016),
    	(5, 2017),
    	(6, 2017),
    	(7, 2017)
    ;

**Query 1**:

    SELECT
          AssocNum
        , SUM(Value) AS sum_value
    FROM SourceData AS d
          INNER JOIN AssoociatedData AS a
                      ON d.AssocNum = a.Num
    GROUP BY
          AssocNum
    

**[Results][2]**:
    
    | ASSOCNUM | SUM_VALUE |
    |----------|-----------|
    |     2015 |       200 |
    |     2016 |       200 |
    |     2017 |       300 |


**Query 2**:

    SELECT
          AssocNum
        , SUM(Value) over() AS sum_value
    FROM SourceData AS d
          INNER JOIN AssoociatedData AS a
                      ON d.AssocNum = a.Num
    

**[Results][3]**:
    
    | ASSOCNUM | SUM_VALUE |
    |----------|-----------|
    |     2015 |       700 |
    |     2015 |       700 |
    |     2016 |       700 |
    |     2016 |       700 |
    |     2017 |       700 |
    |     2017 |       700 |
    |     2017 |       700 |


**Query 3**:

    SELECT DISTINCT
          AssocNum
        , SUM(Value) over() AS sum_value
    FROM SourceData AS d
          INNER JOIN AssoociatedData AS a
                      ON d.AssocNum = a.Num
    

**[Results][4]**:
    
    | ASSOCNUM | SUM_VALUE |
    |----------|-----------|
    |     2015 |       700 |
    |     2016 |       700 |
    |     2017 |       700 |


**Query 4**:

    SELECT
          AssocNum
        , SUM(Value) AS sum_value
    FROM SourceData AS d
          INNER JOIN (SELECT DISTINCT Num FROM AssoociatedData) AS a
                      ON d.AssocNum = a.Num
    GROUP BY
          AssocNum
    

**[Results][5]**:
    
    | ASSOCNUM | SUM_VALUE |
    |----------|-----------|
    |     2015 |       100 |
    |     2016 |       100 |
    |     2017 |       100 |



  [1]: http://sqlfiddle.com/#!3/19e42/5

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40205833
It's solved?
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40206403
To be honest, no.  I'm just going to move on and ask one of my T-SQL colleagues to help me out with this or even ask him to take over the project.  This stuff is beyond my comprehension.

Goodbye.

TBSupport
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 78
partitioning database after decade growth 8 79
SQL QUERY 3 43
Database Availability Group Distribution 9 49
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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