May 25, 2016 at 6:36 pm
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 Microsoft Dynamics 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 attachment and if you look at the "Period 1" that I created in that attachment, 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
;
May 25, 2016 at 9:54 pm
Quick question, can you post the DDL (create script(s)) for the tables, sample data as insert script(s) and the expected results?
😎
May 26, 2016 at 12:37 am
SUM(-GL10111.CRDTAMNT + GL10111.DEBITAMT + PREV.PERDBLNC - GL10111.PERDBLNC) - ( GL10111.DEBITAMT - GL10111.CRDTAMNT )
Something does not look right here.
_____________
Code for TallyGenerator
May 26, 2016 at 9:39 am
Hi There!
I have eight attachments, here on this post. The six queries are sample data that you requested.
The last two attachments are Excel spreadsheets. The first shows what SQL is displaying, for this query where ACTINDX = 1 and YEAR1 = 2012.
The second shows the data expected. I have highlighted, in the second spreadsheet, the rows containing "missing" data for Opening Balance and Ending Balance.
For the "Insert GL10111" script, I have three years represented--2011, 2012, and 2013.
Thanks, so much, for your help! I really appreciate it!
John
May 27, 2016 at 6:26 pm
Hi There:
Actually, thanks to someone named "RogerRogerATX" on the Microsoft Dynamics Community message board, I was able to get this figured out thanks to the following code that he and I worked on for SQL 2008:
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 > 2010
) p1
LEFT OUTER JOIN GL10111 g ON p1.YEAR1 = g.YEAR1
AND p1.PERIODID = g.PERIODID
AND p1.ACTINDX = g.ACTINDX
)
select
g.ACTNUMST AS [Account Number]
,d.ACTDESCR as [Account Description]
,a.YEAR1 As Year
,a.PERIODID as Period
,b.NetChange - a.netchange as [Opening Balance]
,a.debitamnt AS [Debit]
,a.crdtamnt AS [Credit]
,a.netchange as [Net Change]
,b.NetChange as [Ending Balance]
FROM allbalances a
inner JOIN GL00105 g on a.ACTINDX = g.ACTINDX
inner JOIN GL00100 d on a.ACTINDX = d.ACTINDX
CROSS APPLY (SELECT SUM(netchange) AS NetChange
FROM allbalances b WHERE a.ACTINDX=b.ACTINDX AND b.PERIODDT <= a.PERIODDT
and a.YEAR1=b.YEAR1) b
ORDER BY g.ACTNUMST,a.YEAR1,a.PERIODID
Thanks!
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply