Control SQL - Find GL Out of Balance

This query is run to identify any places the GL:

• Is Out of Balance Overall
• Is Out of Balance for a Division
• Will result in an improper Balance Sheet/Income Statement
  • This part not implemented yet
• Uses any non-existend GL Accounts
  • This part not implemented yet

None. This is a selection query and no data is modified in the running of it.

-- GL Out of Balance Breakdown
 
 
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '1/1/2008'
SET @EndDate   = '1/1/2020'
 
SELECT
    (SELECT DivisionName AS Division
        FROM EmployeeGroup
        WHERE OOBGL.DivisionID = EmployeeGroup.ID) AS Division,
    CAST(
        CAST(OOBGL.TheMonth AS VARCHAR(2))
         + '/' + CAST(OOBGL.TheDay AS VARCHAR(2))
         + '/' + CAST(OOBGL.TheYear AS VARCHAR(4))
        AS DateTime ) AS TheDate,
    OOBGL.TheHour,
    TransHeader.OrderNumber,
    TransHeader.BillNumber,
    OOBGL.OOBAmount,
    OOBGL.OffBalanceSheet,
    Account.CompanyName,
    TransHeader.Description,
    TransHeader.AccountID,
    OOBGL.TransHeaderID,
    (SELECT MAX(EntryDateTime)
        FROM GL GL2
        WHERE GL2.TransactionID = OOBGL.TransHeaderID) AS LastGLEntryDate
FROM
(
    SELECT    COALESCE(DivisionID, 10) AS DivisionID,
            COALESCE (TransactionID, 0) AS TransHeaderID,
            DatePart(YEAR, EntryDateTime) AS TheYear,
            DatePart(MONTH, EntryDateTime) AS TheMonth,
            DatePart(DAY, EntryDateTime) AS TheDay,
            DatePart(HOUR, EntryDateTime) AS TheHour,
            OffBalanceSheet,
            SUM(Amount) AS OOBAmount
    FROM    Ledger
    WHERE EntryDateTime BETWEEN @StartDate AND @EndDate
    GROUP BY COALESCE(DivisionID, 10),
        COALESCE (TransactionID, 0),
        DatePart(YEAR, EntryDateTime),
        DatePart(MONTH, EntryDateTime),
        DatePart(DAY, EntryDateTime),
        DatePart(HOUR, EntryDateTime),
        OffBalanceSheet
    HAVING    (SUM(Amount) <> 0)
) AS OOBGL
 
LEFT OUTER JOIN TransHeader ON TransHeader.ID = OOBGL.TransHeaderID
LEFT OUTER JOIN Account ON TransHeader.AccountID = Account.ID
ORDER BY Division, TheDate DESC, TheHour DESC, OrderNumber, BillNumber

• Entered : / /2009
• Version :