T-SQL: Displaying Data from a Previous Row

(0) Share

Report

Posted on by Community Member

Hello:

Below is code for a CTE "table" that I have for displaying for one account and one year the Opening Balance, Debit, Credit, and Ending Balance for each period of that year.

The GL10111 table in SQL, though, does not display records (i.e. periods) where there is no transaction activity. The History Summary Inquiry window in GP does show all periods, whether there are transactions or not. But, again, this table does not.

So, I'm using programming from this CTE "table" to essentially create those periods.

The problem that I'm having is that I need, for each such "created" period to show the Opening Balance and Ending Balance from the previous row.

As you can see from the screenshot below and if you look at the "Period 1" that I created, I have not been successful in doing this.

I need for 209805.93 to appear for the Opening Balance and Ending Balance of Period 1.

Can someone please let me know what I need to tweak in the programming to make this happen?

Thanks!

John

WITH y as (select 2012 as yno)

, p as (

select 0 as pno union all

select 1 as pno union all

select 2 as pno union all

select 3 as pno union all

select 4 as pno union all

select 5 as pno union all

select 6 as pno union all

select 7 as pno union all

select 8 as pno union all

select 9 as pno union all

select 10 as pno union all

select 11 as pno union all

select 12 as pno

)

SELECT

GL.ACTINDX AS [Account Index]

, y.yno AS [Year]

, p.pno AS [Period]

, isnull(CASE

WHEN p.pno = 0 THEN GL10111.PERDBLNC

ELSE SUM(-GL10111.CRDTAMNT + GL10111.DEBITAMT + PREV.PERDBLNC - GL10111.PERDBLNC) - (GL10111.DEBITAMT - GL10111.CRDTAMNT)

END,0) AS [Opening Balance]

, isnull(CASE

WHEN p.pno = 0 THEN 0

ELSE GL10111.DEBITAMT

END,0) AS [Debit]

, isnull(CASE

WHEN p.pno = 0 THEN 0

ELSE GL10111.CRDTAMNT

END,0) AS [Credit]

, isnull(CASE

WHEN p.pno = 0 THEN 0

ELSE GL10111.DEBITAMT - GL10111.CRDTAMNT

END,0) AS [Net Change]

, isnull(CASE

WHEN p.pno = 0 THEN GL10111.PERDBLNC

ELSE SUM(-GL10111.CRDTAMNT + GL10111.DEBITAMT + PREV.PERDBLNC - GL10111.PERDBLNC)

END,0) AS [Ending Balance]

, GL00105.ACTNUMST AS [Account]

, GL00100.ACTDESCR AS [Account Description]

FROM y

CROSS JOIN p

INNER JOIN (SELECT DISTINCT

ACTINDX, YEAR1

FROM GL10111

WHERE GL10111.YEAR1 = 2012

) GL ON y.yno = GL.YEAR1

INNER JOIN GL00100 ON GL.ACTINDX = GL00100.ACTINDX

INNER JOIN GL00105 ON GL.ACTINDX = GL00105.ACTINDX

LEFT JOIN GL10111 ON GL.ACTINDX = GL10111.ACTINDX

AND y.yno = GL10111.YEAR1

AND p.pno = GL10111.PERIODID

LEFT JOIN GL10111 AS PREV ON GL10111.ACTINDX = PREV.ACTINDX

AND GL10111.YEAR1 = PREV.YEAR1

AND GL10111.PERIODID >= PREV.PERIODID

GROUP BY

GL.ACTINDX

, y.yno

, p.pno

, GL10111.DEBITAMT

, GL10111.CRDTAMNT

, GL10111.PERDBLNC

, GL00105.ACTNUMST

, GL00100.ACTDESCR

ORDER BY

GL.ACTINDX

, y.yno

, p.pno

;

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (1)

Community Member on at

Like (0)

Report

Hi John, I've done something similar to what you're describing, and am happy to share my script here. I used the SY40102 table to get the period list from 1 to 12, and created a CTE to get all combinations of period and GL. It uses LAG, so it probably won't work on SQL versions older than 2012. I hope you find this useful.

declare @theyear int

set @theyear = 2012
;
--CTE get every account and periodid combination for the year
with allaccounts as(
select g.actindx, p.periodid, p.year1 from sy40102 p
cross join gl00100 g
where p.series = 0 and p.year1 = @theyear
)
,
--CTE get period movement with running total of net change
allbalances as(

select isnull(g.actindx,p.actindx) as ACTINDX, p.year1, p.periodid, isnull(g.debitamt,0) as debitamnt, isnull(g.crdtamnt,0) as crdtamnt, isnull(g.perdblnc,0) as netchange
, sum(isnull(g.perdblnc,0)) over(partition by isnull(g.actindx,p.actindx) order by isnull(g.actindx,p.actindx), p.year1, p.periodid ) as ClosingBalance
from
allaccounts p left outer join
gl10111 g on p.YEAR1 = g.YEAR1 and p.periodid = g.periodid and p.actindx = g.actindx)

--final select with lag for opening balance: previous cb = ob
select actindx, year1, periodid, debitamnt, crdtamnt, netchange, lag(closingbalance) over(partition by actindx order by actindx, year1, periodid) as OpeningBalance,
ClosingBalance from allbalances
order by actindx, year1, periodid

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Alicia:

Thank you, so much, for the quick response! I really appreciate it!

Is there a way of using your code without variables?

John

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi John. Yes you can have the year (or list of years) hard-coded into the script. The code below gives you data for years 2012 and 2013. If you just want a single year, you can edit the WHERE clause to:

where p.series = 0 and p.year1 = 2012

--CTE get every account and periodid combination for the year

with allaccounts as(

select g.actindx, p.periodid, p.year1 from sy40102 p

cross join gl00100 g

where p.series = 0 and p.year1 in(2012,2013)

)

,

--CTE get period movement with running total of net change

allbalances as(

select isnull(g.actindx,p.actindx) as ACTINDX, p.year1, p.periodid, isnull(g.debitamt,0) as debitamnt, isnull(g.crdtamnt,0) as crdtamnt, isnull(g.perdblnc,0) as netchange

, sum(isnull(g.perdblnc,0)) over(partition by isnull(g.actindx,p.actindx) order by isnull(g.actindx,p.actindx), p.year1, p.periodid ) as ClosingBalance

from

allaccounts p left outer join

gl10111 g on p.YEAR1 = g.YEAR1 and p.periodid = g.periodid and p.actindx = g.actindx)

--final select with lag for opening balance: previous cb = ob

select actindx, year1, periodid, debitamnt, crdtamnt, netchange, lag(closingbalance) over(partition by actindx order by actindx, year1, periodid) as OpeningBalance,

ClosingBalance from allbalances

order by actindx, year1, periodid

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Alicia:

We're on SQL 2008. So, the "lag" function does not work. Is there a substitute?

Also, SQL is giving me a syntax error "near order", for the clause below. That may also be because we're on SQL 2008. Any ideas?

order by isnull(g.ACTINDX,p.ACTINDX), p.YEAR1, p.PERIODID) as ClosingBalance

John

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yep, both those errors will be due to the SQL version. Try this one. It uses a # temp table. I tried it with using CTE's only, but it was VERY slow.

with allaccounts as(

select g.actindx, p.periodid, p.year1 from sy40102 p

cross join gl00100 g

where p.series = 0 and p.year1 in(2013,2014)

)

,

allbalances as(

select isnull(g.actindx,p.actindx) as ACTINDX, p.year1, p.periodid, isnull(g.debitamt,0) as debitamnt, isnull(g.crdtamnt,0) as crdtamnt, isnull(g.perdblnc,0) as netchange

from

allaccounts p left outer join

gl10111 g on p.YEAR1 = g.YEAR1 and p.periodid = g.periodid and p.actindx = g.actindx)

select a.actindx, a.year1, a.periodid, a.debitamnt, a.crdtamnt, a.netchange

into #temptable

from allbalances a

order by actindx, year1, periodid;

with ctebalances as(

select

a.* ,

(select SUM(b.netchange) from #temptable b where b.PERIODID <= a.PERIODID and b.YEAR1 = a.YEAR1 and b.ACTINDX = a.ACTINDX) as ClosingBalance

from #temptable a)

select a.ACTINDX, g.ACTNUMST, a.year1, a.periodid, a.debitamnt, a.crdtamnt,

(select b.ClosingBalance from ctebalances b where b.PERIODID = a.PERIODID-1 and b.YEAR1 = a.YEAR1 and b.ACTINDX = a.ACTINDX) as OpeningBalance,

a.netchange, a.ClosingBalance

from ctebalances a

inner join GL00105 g on a.ACTINDX = g.ACTINDX

order by ACTINDX, YEAR1, PERIODID

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Alicia:

I appreciate your hard work, on this. Thank you! Unfortunately, the query returned no results. :(

John

Was this reply helpful? Yes No
RogerRogerATX 1,515 on at

Like (0)

Report

John, try sy40100 instead of sy40102.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Roger:

Is it possible to do this, without having to create a temp table?

John

Was this reply helpful? Yes No
RogerRogerATX 1,515 on at

Like (0)

Report

Maybe.

I *think* SQL2008 has windowing:

WITH allbalances AS (

SELECT ISNULL(g.ACTINDX,p1.ACTINDX) AS ACTINDX

,p1.YEAR1

,p1.PERIODID

,ISNULL(g.DEBITAMT,0) AS debitamnt

,ISNULL(g.CRDTAMNT,0) AS crdtamnt

,ISNULL(g.PERDBLNC,0) AS netchange

,p1.PERIODDT

FROM (SELECT g.ACTINDX

,p.PERIODID

,p.YEAR1

,p.PERIODDT

FROM SY40100 p

CROSS JOIN GL00100 g

WHERE p.SERIES = 0

AND p.YEAR1 IN (2013,2014)

) p1

LEFT OUTER JOIN GL10111 g ON p1.YEAR1 = g.YEAR1

AND p1.PERIODID = g.PERIODID

AND p1.ACTINDX = g.ACTINDX)

select

g.ACTNUMST

,a.YEAR1

,a.PERIODID

,a.debitamnt AS [Debit Amt]

,a.crdtamnt AS [Credit Amt]

,a.netchange

,SUM(a.netchange) OVER (PARTITION BY G.ACTNUMST ORDER BY G.ActNUMST,a.PERIODDT) AS [Balance To Date]

FROM allbalances a

inner JOIN GL00105 g on a.ACTINDX = g.ACTINDX

ORDER BY g.ACTNUMST,Year1,a.PERIODID

try this

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Roger:

Thanks! Unfortunately, SQL 2008 does not support the clause below. So, I'm getting a syntax error near the word "order".

Is there a way to rewrite that clause, for SQL 2008?

Thanks!

John

,SUM(a.netchange) OVER (PARTITION BY G.ACTNUMST ORDER BY G.ActNUMST,a.PERIODDT)

Was this reply helpful? Yes No