July 17, 2014 at 2:47 pm
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'
July 17, 2014 at 3:49 pm
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.
July 17, 2014 at 3:54 pm
That article offers very little, in terms of anything along the lines of examples or knowledge transfer.
I await another response.
Thank you.
John
July 17, 2014 at 4:00 pm
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.
July 18, 2014 at 2:18 am
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'
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
July 18, 2014 at 3:33 am
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