Share
ReportHello:
Below is CTE code that I tried to learn, from reading a blog. Also, I have pasted an image from SQL.
I need help on the "Ending Balance" column. This column is supposed to be a running sum. The column is correct, for the first two rows.
In other words, Row 2 correctly adds the Ending Balance of Row 1 plus the Period Balance of Row 2.
But, Row 3 begins a pattern where it simply adds the "Period Balance" to the "Previous Period Balance" column. What I want is to add the "Ending Balance" of the previous row to the "Period Balance" of each succeeding row. That's what I mean by running sum.
How do I do this?
Thanks!
WITH CTE AS (
SELECT
Row = ROW_NUMBER() over (partition by GL10111.ACTINDX, GL10111.YEAR1 order by GL10111.PERIODID), GL10111.PERDBLNC, GL10111.ACTINDX, GL10111.YEAR1, GL10111.PERIODID
FROM GL10111
INNER JOIN GL00100 ON GL10111.ACTINDX = GL00100.ACTINDX
INNER JOIN GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
WHERE GL10111.YEAR1 = 2012 and GL10111.ACTINDX = 1
)
SELECT CTE.Row,
CTE.PERIODID as [Period ID],
CTE.PERDBLNC as [Period Balance],
COALESCE(prev.PERDBLNC, 0) as [Previous Period Balance],
CASE WHEN CTE.PERIODID = 0 THEN CTE.PERDBLNC ELSE CTE.PERDBLNC + COALESCE(prev.PERDBLNC, 0) end as [Ending Balance]
FROM CTE
LEFT JOIN CTE prev ON prev.Row = CTE.Row - 1
LEFT JOIN CTE nex ON nex.Row = CTE.Row + 1
*This post is locked for comments
I have the same question (0)
All responses (
Answers (
2
