Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

T-SQL: Cumulative SUM

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
TBSupport
Asked:
TBSupport
  • 5
  • 5
1 Solution
 
TBSupportAuthor Commented:
Anyone?  Anyone?  :)

TBSupport
0
 
PortletPaulCommented:
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
 
TBSupportAuthor Commented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
PortletPaulCommented:
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
 
TBSupportAuthor Commented:
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
 
PortletPaulCommented:
"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
 
TBSupportAuthor Commented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
It's solved?
0
 
TBSupportAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now