This is an old revision of the document!
Control SQL - Find GL Out of Balance
Explanation of SQL
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
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
-- 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
Version Information
- Entered : / /2009
- Version :
You could leave a comment if you were logged in.