Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
back_in_the_1980s
Starting Member
1 Post |
 Posted - 2014-07-18 : 15:08:16
|
| Hello:Below is my T-SQL query. I would love to show the results of running it, to make my post easier to understand. But, I cannot figure out how to do so. Hopefully, you all can understand what I need or give me instructions on how to insert an image on this Forum.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. Those amounts are for the years 2015, 2016, and 2017, respectively.I don't 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!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.BOOKINDXINNER 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' |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-07-18 : 16:02:47
|
| Here are some links that might help with asking your question(s) now and in the future:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
|
|
|
|
|
Cumulative SUM