Link to home
Start Free TrialLog in
Avatar of John Ellis
John Ellis

asked on

T-SQL: Carrying a Figure from One Row to the Next

Hello:

Below is my code that is not working.

The first screenshot following the code shows the data returned.  It is the "Opening Balance" field that is not working properly, for any rows where the Period ID <> 0.  (Where the Period ID = 0, the "Opening Balance" should show as 0 and my code is correctly showing this.)

The second screenshot, basically, shows how the Opening Balance should appear.  If you look at the screenshot, you will see the phrase "Beginning Balance".  This is where Period ID = 0.  So, in my programming, "Opening Balance" for Period 0 is correctly showing as 0.

But, for all other periods (whether for Periods 1 - 12 or Periods 1 - 2 or whatever), the "Opening Balance" should be the sum of the "Ending Balance" from the previous row plus the current row's Debit - the current row's Credit.

For Period 1, the "Opening Balance" is (in reality) 1,012,733.66.  This carries forward the Ending Balance (GL10110.PERDBLNC) from Period 0's row and adds this figure to GL10110.DEBITAMT - GL10110.CRDTAMNT for Period 1.

For Period 2, the "Opening Balance" should likewise display as 1,025,079.12.  This is the "Ending Balance" (i.e. GL10110.PERDBLNC) from Period 1.

I can never figure out how to carry forward a figure from a previous row in T-SQL to add it to the figure of the next row.  As hard as I have tried, I cannot get it to work.

How do I fix my code?

Thank you!

John

SELECT DISTINCT GL00105.ACTNUMST as [Account],
GL00100.ACTDESCR as [Account Description],
GL10110.YEAR1 as [Year], GL10110.PERIODID as [Period],
CASE WHEN GL10110.PERIODID = 0 then 0 
WHEN GL10110.PERIODID = 1 THEN sum(GL10110.PERDBLNC) - sum(GL10110.DEBITAMT - GL10110.CRDTAMNT) 
WHEN GL10110.PERIODID = 2 THEN sum(GL10110.PERDBLNC) - sum(GL10110.DEBITAMT - GL10110.CRDTAMNT) 
end as [Opening Balance],
GL10110.DEBITAMT as [Debit],
GL10110.CRDTAMNT as [Credit],
sum(GL10110.DEBITAMT - GL10110.CRDTAMNT) as [Net Change],
sum(GL10110.PERDBLNC) As [Ending Balance]
FROM GL10110 
INNER JOIN GL00100 ON GL10110.ACTINDX = GL00100.ACTINDX
INNER JOIN GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
WHERE GL10110.YEAR1 = '2017' and GL10110.ACTINDX = '4826'
GROUP BY GL10110.PERIODID, GL10110.ACTINDX, GL00105.ACTNUMST, GL00100.ACTDESCR, GL10110.DEBITAMT,
GL10110.CRDTAMNT, GL10110.YEAR1

Open in new window


User generated image
User generated image
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi John,
Please try this..

SELECT DISTINCT GL00105.ACTNUMST as [Account],
GL00100.ACTDESCR as [Account Description],
GL10110.YEAR1 as [Year], GL10110.PERIODID as [Period],
SUM(CASE 
WHEN GL10110.PERIODID = 0 THEN 0 
ELSE GL10110.PERDBLNC - GL10110.DEBITAMT - GL10110.CRDTAMNT
end) as [Opening Balance],
GL10110.DEBITAMT as [Debit],
GL10110.CRDTAMNT as [Credit],
sum(GL10110.DEBITAMT - GL10110.CRDTAMNT) as [Net Change],
sum(GL10110.PERDBLNC) As [Ending Balance]
FROM GL10110 
INNER JOIN GL00100 ON GL10110.ACTINDX = GL00100.ACTINDX
INNER JOIN GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
WHERE GL10110.YEAR1 = '2017' and GL10110.ACTINDX = '4826'
GROUP BY GL10110.PERIODID, GL10110.ACTINDX, GL00105.ACTNUMST, GL00100.ACTDESCR, GL10110.DEBITAMT,
GL10110.CRDTAMNT, GL10110.YEAR1

Open in new window


Hope it  helps!
Avatar of John Ellis
John Ellis

ASKER

Hi Pawan:
I'm afraid that it's still incorrectly showing 0 as the "Opening Balance" for Period 1.
John
Hi John,
Please try this

;WITH CTE AS
(
	SELECT 
	DISTINCT GL00105.ACTNUMST as [Account],
	GL00100.ACTDESCR as [Account Description],
	GL10110.YEAR1 as [Year]
	, GL10110.PERIODID as [Period]
	GL10110.DEBITAMT as [Debit],
	GL10110.CRDTAMNT as [Credit]
	FROM GL10110 
	INNER JOIN GL00100 ON GL10110.ACTINDX = GL00100.ACTINDX
	INNER JOIN GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
	WHERE GL10110.YEAR1 = '2017' and GL10110.ACTINDX = '4826'
	GROUP BY GL10110.PERIODID, GL10110.ACTINDX, GL00105.ACTNUMST, GL00100.ACTDESCR, GL10110.DEBITAMT,
	GL10110.CRDTAMNT, GL10110.YEAR1
)
SELECT * , CASE WHEN PERIODID = 0 THEN 0 ELSE t.OriginalBalance END NewOriginalBalance FROM CTE a
CROSS APPLY
(
	SELECT sum(GL10110.PERDBLNC) - sum(GL10110.DEBITAMT - GL10110.CRDTAMNT) [OriginalBalance] , sum(b.DEBITAMT - b.CRDTAMNT) as [Net Change]
	, sum(b.PERDBLNC) As [Ending Balance] 
	FROM GL10110 b
	WHERE a.Account = b.Account
)t

Open in new window


Hope it helps!
Hi Pawan:

It's giving me a syntax error, as follows:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'GL10110'.

John
Hi John,
Please try this-

SELECT * , CASE WHEN [Period] = 0 THEN 0 ELSE [Opening Balance] END [Opening Balance1] FROM 
(
	SELECT DISTINCT 
	GL00105.ACTNUMST as [Account],
	GL00100.ACTDESCR as [Account Description],
	GL10110.YEAR1 as [Year]
	, GL10110.PERIODID as [Period],
	sum(GL10110.PERDBLNC) - sum(GL10110.DEBITAMT - GL10110.CRDTAMNT) [Opening Balance]
	GL10110.DEBITAMT as [Debit],
	GL10110.CRDTAMNT as [Credit],
	sum(GL10110.DEBITAMT - GL10110.CRDTAMNT) as [Net Change],
	sum(GL10110.PERDBLNC) As [Ending Balance]
	FROM GL10110 
	INNER JOIN GL00100 ON GL10110.ACTINDX = GL00100.ACTINDX
	INNER JOIN GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
	WHERE GL10110.YEAR1 = '2017' and GL10110.ACTINDX = '4826'
	GROUP BY GL00105.ACTNUMST,GL00100.ACTDESCR,GL10110.YEAR1,GL10110.PERIODID
)X

Open in new window


Hope it helps!
I'm still getting incorrect syntax error.

Are the queries that you're posting complete, or am I to "add on" to them.  

I'm unclear.

Can the original query that I posted simply not be "tweaked"?

John
Avatar of Shaun Kline
Try this:
WITH Data AS (
    SELECT DISTINCT GL00105.ACTNUMST as [Account],
        GL00100.ACTDESCR as [Account Description],
        GL10110.YEAR1 as [Year], GL10110.PERIODID as [Period],
        GL10110.DEBITAMT as [Debit],
        GL10110.CRDTAMNT as [Credit],
        GL10110.DEBITAMT - GL10110.CRDTAMNT as [Net Change],
        GL10110.PERDBLNC
    FROM GL10110 
        INNER JOIN GL00100 ON GL10110.ACTINDX = GL00100.ACTINDX
        INNER JOIN GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
    WHERE GL10110.YEAR1 = '2017' and GL10110.ACTINDX = '4826')
SELECT Data.[Account], Data.[Account Description], Data.[Year],
    Data.[Period], ISNULL(Opening.Balance, 0) [Opening Balance],
    Data.[Debit], Data.[Credit], Data.[Net Change],
    ISNULL(Ending.Balance, 0) [Ending Balance]
FROM Data
    CROSS APPLY (
        SELECT Balance = SUM(PERDBLNC)
        FROM Data D
        WHERE [Period] < Data.Period) Opening
    CROSS APPLY (
        SELECT Balance = SUM(PERDBLNC)
        FROM Data D
        WHERE [Period] <= Data.Period) Ending
    

Open in new window

This query uses data from your original query, removes the grouping and aggregates in the CTE (Data) query, and then performs the summing you desire in the CROSS APPLY queries.
Hi John,
Please try this..Sorry about the errors , comma was missed.

Looks like Shaun's query did the trick. Any way please try below-

--
;WITH CTE AS
(
	SELECT 
	 GL00105.ACTNUMST as [Account]
	,GL00100.ACTDESCR as [Account Description]
	,GL10110.YEAR1 as [Year]
	,GL10110.PERDBLNC
	,GL10110.PERIODID as [Period]
	,GL10110.DEBITAMT as [Debit]
	,GL10110.CRDTAMNT as [Credit]
	FROM GL10110 
	INNER JOIN GL00100 ON GL10110.ACTINDX = GL00100.ACTINDX
	INNER JOIN GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
	WHERE GL10110.YEAR1 = '2017' and GL10110.ACTINDX = '4826'
	GROUP BY 
	 GL00105.ACTNUMST 
	,GL00100.ACTDESCR 
	,GL10110.YEAR1 
	,GL10110.PERIODID 
	,GL10110.DEBITAMT
	,GL10110.CRDTAMNT
	,GL10110.PERDBLNC
)
SELECT * , CASE WHEN a.[Period] = 0 THEN 0.0 ELSE CAST(k1.b1 AS sql_variant) END [OpeningBalance] , k2.b2 [ClosingBalance] FROM CTE a
CROSS APPLY ( SELECT SUM(b.PERDBLNC) b1 FROM CTE b WHERE b.[Period] < a.Period  ) k1
CROSS APPLY ( SELECT SUM(b.PERDBLNC) b2 FROM CTE b WHERE b.[Period] <= a.Period ) k2
--

Open in new window


Hope it helps!
What version of sql server are you using?
I believe sql server 2012 introduced the lag function, which you can use to get previous ending balance as current opening balance
select case when period = 0 then 0 else lag(ending_balance) over (partition by account order by period) end as opening balance.
Actually, I do have a problem that I just now saw.

If I leave in the "WHERE" clause that restricts the data based on GL10110.YEAR1 = '2017' and GL10110.ACTINDX = '4826', the Opening Balance appears correctly.

But, when I remove this WHERE clause, Opening Balance shows as all 0's.

That, of course, does not work.  Does anyone have any ideas?

Thanks, to all of you for your help!  I really, really appreciate it!

John
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello:

Actually, I'm still having trouble with the Opening Balance.  It's my fault.

I was wrong, when I said that the Opening Balance should always equal 0 when the Period ID equals 0.  Actually, when the Period ID equals 0, the Opening Balance should always equal the Ending Balance as developed from the code below that Shaun helped me with.

Further, the Opening Balance for every subsequent row should of course and obviously be the Ending Balance of the previous row.

How do I fix the code below, to accomplish both of these tasks.

I apologize, for the confusion.  I'm overwhelmed by my work, and I do not understand T-SQL very well.  If I could get training on it, that would be great.  But, whenever I have asked for ideas for training, no one has been able to tell me anything other than "we learn by doing".

That's a very poignant quote, but I'm afraid that that doesn't help me any.

Thanks!

John

WITH CTE AS (
    SELECT DISTINCT KBUK..GL00105.ACTNUMST as [Account],
        KBUK..GL00100.ACTDESCR as [Account Description],
        KBUK..GL10110.YEAR1 as [Year], KBUK..GL10110.PERIODID as [Period],
        KBUK..GL10110.DEBITAMT as [Debit],
        KBUK..GL10110.CRDTAMNT as [Credit],
        KBUK..GL10110.DEBITAMT - KBUK..GL10110.CRDTAMNT as [Net Change],
        KBUK..GL10110.PERDBLNC,
        KBUK..GL10110.ACTINDX
    FROM KBUK..GL10110 
        INNER JOIN KBUK..GL00100 ON KBUK..GL10110.ACTINDX = KBUK..GL00100.ACTINDX
        INNER JOIN KBUK..GL00105 ON KBUK..GL00100.ACTINDX = KBUK..GL00105.ACTINDX
    )
SELECT CTE.[Account], CTE.[Account Description], CTE.[Year],
    CTE.[Period], ISNULL(Opening.Balance, 0) As [Opening Balance],
    CTE.[Debit], CTE.[Credit], CTE.[Net Change],
    ISNULL(Ending.Balance, 0) As [Ending Balance]
FROM CTE
    CROSS APPLY (
        SELECT Balance = SUM(PERDBLNC)
        FROM CTE D
        WHERE [Period] = CTE.Period
            AND [Year] = CTE.Year
            AND ACTINDX = CTE.ACTINDX) as Opening
    CROSS APPLY (
        SELECT Balance = SUM(PERDBLNC)
        FROM CTE D
        WHERE [Period] <= CTE.Period
            AND [Year] = CTE.Year
            AND ACTINDX = CTE.ACTINDX) as Ending 
            ORDER BY [Account], [Period]

Open in new window


User generated image
I suggest asking a new question, providing a link to this question, and provide a more detailed explanation of what period 0 is and how it differs from periods 1 - 12 (months?).
Please post what version of sql server you're using as the lag function can simplify this type of operation dramatically.