Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / T-SQL: Displaying Data...
Microsoft Dynamics GP (Archived)

T-SQL: Displaying Data from a Previous Row

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

CTE-Again.PNG

 

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: T-SQL: Displaying Data from a Previous Row

    By the way, the initial code that I submitted at the beginning of this post was composed by Paul Maxwell of www.experts-exchange.com.

    John

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: T-SQL: Displaying Data from a Previous Row

    Hi Again, Roger:

    I think that I fixed this!

    I simply added a "qualifier" or whatever to the clause saying a.YEAR1 = b.YEAR1 as follows:

    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

    This allowed for the "reset" to happen, for each new year.

    So, I think that I'm finally all set.

    Thanks, again!

    John

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: T-SQL: Displaying Data from a Previous Row

    Hi Roger:

    Actually, I took this code a step further, in order to show data for more than one fiscal year and specifically for all years greater than 2010.  

    When I reviewed the results, it actually turned out to be not what I need after all.  :(

    Below is the code.  The problem is that the CROSS APPLY, where it is summing GL10111's PERDBLNC, needs to reset after one year has passed.  This regards the following clause:

    CROSS APPLY (SELECT SUM(netchange) AS NetChange

    For each new year, the "Opening Balance" for Period 0 needs to be 0, while the "Ending Balance for Period 0 should be the Net Change (i.e. the Debit minus the Credit).

    And, the "Opening Balance" for Period 1 for the new year should simply bring over the prior period's "Ending Balance".  And, Period 1's "Ending Balance" should be its "Opening Balance" combined with the period's "Net Change".

    This math needs to continue, for each succeeding period, until the next new year when everything should "reset" again.

    Instead, the query is summing the PERDBLNC for each account across each year, rather than resetting for the new year.  (I hope that makes sense.)

    I tried to fix this myself by doing a "GROUP BY" on YEAR1.  But, when I run the query, it is still continuing to run without any results returned.  I imagine that it's going to timeout eventually.

    I'm sorry.  I should have mentioned that I was planning on using this for more than one fiscal year.  I confined it to one year, to keep the initial results returned simplified, until I could get working code to use for more than one year.

    Please let me know if and how this is possible in SQL 2008.

    We're close.  We just need to get this to work for more than one fiscal year.

    Thanks, Roger!

    John

    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

                  --IN (2012) AND ACTINDX = 1

          ) 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) b

    ORDER BY g.ACTNUMST,a.YEAR1,a.PERIODID

  • RogerRogerATX Profile Picture
    RogerRogerATX 1,515 on at
    RE: T-SQL: Displaying Data from a Previous Row

    You're welcome John - I've found that having the freedom to learn by doing helps...as well as having access to the core functionality of SQL (temp tables cough cough) helps

    You should drop a hint to upgrade to SQL 2016 later this year (we're on 2014 but if GP is staying is still up in the air so don't know if we will upgrade late in the year)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: T-SQL: Displaying Data from a Previous Row

    Hi Roger:

    That works perfectly!  I did have to add the clause below, to get the Opening Balance.

    But, again, this gets me exactly what I needed!

    Thank you, so much!

    Now, if I can study this query and learn how the syntax behind it works, I'd be set for life.  LOL!!!

    Thanks, again!

    John

    b.NetChange - a.netchange as [Opening Balance]

  • Verified answer
    RogerRogerATX Profile Picture
    RogerRogerATX 1,515 on at
    RE: T-SQL: Displaying Data from a Previous Row

    John, I think SQL Server 2008 has Cross Apply

    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

    ,[Balance to Date] = b.NetChange

    FROM allbalances a

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

    CROSS APPLY (SELECT SUM(netchange) AS NetChange

    FROM allbalances b WHERE a.ACTINDX=b.ACTINDX AND b.PeriodDt <= a.PERIODDT) b

    ORDER BY g.ACTNUMST,a.YEAR1,a.PERIODID

  • RogerRogerATX Profile Picture
    RogerRogerATX 1,515 on at
    RE: T-SQL: Displaying Data from a Previous Row

    Yuck, it needs the order by (added in SQL 2012, but partition works in 2008) to work.

    Still thinking.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: T-SQL: Displaying Data from a Previous Row

    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)

  • RogerRogerATX Profile Picture
    RogerRogerATX 1,515 on at
    RE: T-SQL: Displaying Data from a Previous Row

    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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: T-SQL: Displaying Data from a Previous Row

    Hi Roger:

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

    John

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans