SQL Query for GL Summary Balances

| 11/09/2010 | 2 Comments

Hi all

The first post for today is an SQL script for viewing the summary balances for all GL accounts and for all fiscal years and periods, with both the net change amount and the period balance (cumulative) amounts.

SELECT  Z.*
FROM    ( SELECT    RTRIM(B.[ACTNUMST]) AS GPACCOUNTNO,
                    RTRIM(C.[ACTDESCR]) AS DESCRIPTION,
                    A.[YEAR1] AS FISCALYEAR,
                    A.[PERIODID] AS FISCALPERIOD,
                    E.[PERNAME] AS FISCALPERIODNAME,
                    ISNULL(A.[PERDBLNC], 0) AS NETCHANGE,
                    ( SELECT    ISNULL(SUM(D.[PERDBLNC]), 0)
                      FROM      [dbo].[GL10110] D
                      WHERE     D.[ACTINDX] = A.[ACTINDX]
                                AND D.[YEAR1] = A.[YEAR1]
                                AND D.[PERIODID] <= A.[PERIODID]
                    ) AS PERIODBALANCE
          FROM      [dbo].[GL10110] A
                    INNER JOIN [dbo].[GL00105] B ON B.[ACTINDX] = A.[ACTINDX]
                    INNER JOIN [dbo].[GL00100] C ON C.[ACTINDX] = A.[ACTINDX]
                    INNER JOIN [dbo].[SY40100] E ON E.[YEAR1] = A.[YEAR1]
                                                    AND E.[PERIODID] = A.[PERIODID]
                                                    AND E.[SERIES] = 0
          UNION ALL
          SELECT    RTRIM(B.[ACTNUMST]) AS GPACCOUNTNO,
                    RTRIM(C.[ACTDESCR]) AS DESCRIPTION,
                    A.[YEAR1] AS FISCALYEAR,
                    A.[PERIODID] AS FISCALPERIOD,
                    E.[PERNAME] AS FISCALPERIODNAME,
                    ISNULL(A.[PERDBLNC], 0) AS NETCHANGE,
                    ( SELECT    ISNULL(SUM(D.[PERDBLNC]), 0)
                      FROM      [dbo].[GL10111] D
                      WHERE     D.[ACTINDX] = A.[ACTINDX]
                                AND D.[YEAR1] = A.[YEAR1]
                                AND D.[PERIODID] <= A.[PERIODID]
                    ) AS PERIODBALANCE
          FROM      [dbo].[GL10111] A
                    INNER JOIN [dbo].[GL00105] B ON B.[ACTINDX] = A.[ACTINDX]
                    INNER JOIN [dbo].[GL00100] C ON C.[ACTINDX] = A.[ACTINDX]
                    INNER JOIN [dbo].[SY40100] E ON E.[YEAR1] = A.[YEAR1]
                                                    AND E.[PERIODID] = A.[PERIODID]
                                                    AND E.[SERIES] = 0
        ) Z
ORDER BY Z.[GPACCOUNTNO],
        Z.[FISCALYEAR],
        Z.[FISCALPERIOD]

All these balances that you see are in functional currencies. I am working on a script to provide details of the multi-currency summary details as well. Will post it once completed.

Until next post…

VN:F [1.9.22_1171]
Rating: 4.8/10 (4 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)
SQL Query for GL Summary Balances, 4.8 out of 10 based on 4 ratings

Tags: , , ,

Category: General Ledger, SQL Server, SQL Server 2005, SQL Server 2008

About the Author ()

  • http://www.facebook.com/LostInDaJungle Jason Maggard

    Hi, I’m using your SQL to feed an Excel file. I get all of the account balances and then summarize them with Pivot Tables to get YTD. The problem I’ve run into is that for accounts with no activity, there is no line for that month. So my pivot table might only have months 1,3,7 for 2012. When I try to get the YTD using:

    “=GETPIVOTDATA(“YTD”,ptAll,”FISCALYEAR”,2012,”FISCALPERIOD”,6,”GPACCOUNTNO”,”71100200″)”

    I get #REF errors if there was no activity for that month. (Because the month doesn’t “exist” in my pivot table.) How would I modify this to fill in the missing months with “0″ for NETCHANGE so that I can run my YTD calcs? I have tried a few things, but nothing seems to be working for me.

    Thank you very much for your time and help.

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)

  • Pingback: DynamicAccounting.net

«
»