You’re offline. This is a read only version of the page.
Hello:
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)
Disregard. Steve Erbach helped me figure this out, as follows:
SELECT GL10111.ACTINDX as [Account Index], GL00105.ACTNUMST as [Account],
GL00100.ACTDESCR as [Account Description],
GL10111.YEAR1 as [Year], GL10111.PERIODID as [Period],
case when GL10111.PERIODID = 0 then 0 ELSE
SUM(-GL10111.CRDTAMNT + GL10111.DEBITAMT + PREV.PERDBLNC-GL10111.PERDBLNC)-(GL10111.DEBITAMT-GL10111.CRDTAMNT)
END as [Opening Balance],
CASE WHEN GL10111.PERIODID = 0 THEN 0 ELSE GL10111.DEBITAMT END as [Debit],
CASE WHEN GL10111.PERIODID = 0 THEN 0 ELSE GL10111.CRDTAMNT END as [Credit],
CASE WHEN GL10111.PERIODID = 0 THEN 0 ELSE GL10111.DEBITAMT - GL10111.CRDTAMNT END as [Net Change],
CASE WHEN GL10111.PERIODID = 0 THEN GL10111.PERDBLNC ELSE
SUM(-GL10111.CRDTAMNT + GL10111.DEBITAMT + PREV.PERDBLNC-GL10111.PERDBLNC) END AS [Ending Balance]
FROM GL10111
INNER JOIN GL10111 as PREV
ON GL10111.ACTINDX = PREV.ACTINDX
AND GL10111.YEAR1 = PREV.YEAR1
AND GL10111.PERIODID >= PREV.PERIODID
INNER JOIN GL00100 ON GL10111.ACTINDX = GL00100.ACTINDX
INNER JOIN GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
WHERE GL10111.YEAR1 = 2012
GROUP BY GL10111.PERIODID, GL10111.ACTINDX, GL00105.ACTNUMST, GL00100.ACTDESCR, GL10111.DEBITAMT,
GL10111.CRDTAMNT, GL10111.YEAR1, GL10111.PERDBLNC
ORDER BY GL10111.ACTINDX, GL10111.YEAR1, GL10111.PERIODID
Share
Report
All responses (
Answers (
2
