This SQL is used to pull a summary of all GL changes for a given time period. It can be cut and pasted as a SQL Report in the menus.

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

• Date Range
  • Use - Enter the starting and ending dates for the report.
  • Type - DateTime
  • Recommended Default - Today

SELECT 
  Journal.ID AS [Journal ID],
  Division.DivisionName AS [Division Name],
  GL.EntryDateTime AS [GL DATE],
  JournalActivityText AS [Journal TYPE], 
  Journal.Description AS [Journal Description],
  GLAccount.AccountName AS [Account Name],
  GL.Amount AS [GL Amount],
  CASE WHEN GL.Amount > 0 THEN GL.Amount ELSE 0 END AS [Debit],
  CASE WHEN GL.Amount < 0 THEN -GL.Amount ELSE 0 END AS [Credit],
  GL.Description AS [Line Description],
  TransHeader.OrderNumber AS [ORDER NUMBER],
  Account.CompanyName AS [Company],
  GL.Reconciled AS [Line Reconciled],
  GLAccount.GLClassTypeName AS [GL Class TYPE Name],
  Journal.CompletedDateTime AS [Journal DATE],
  Journal.Notes AS [Journal Notes],
  Journal.IsVoided AS [Journal Voided],
  Journal.VoidedDateTime AS [Voided DATE],
  GL.ID AS [GLID],  
  GL.GLAccountID AS [GLAccountID],
  GL.AccountID AS [AccountID],
  Division.ID AS [DivisionID],
  GL.TransactionID AS [TransHeaderID],
  GL.GLClassificationType AS [GLClassificationTypeID]
 
FROM GL 
LEFT JOIN Journal ON Journal.ID = GL.JournalID
LEFT JOIN GLAccount ON GL.GLAccountID = GLAccount.ID
LEFT JOIN Account ON Account.ID = GL.AccountID
LEFT JOIN TransHeader ON TransHeader.ID = GL.TransactionID
LEFT JOIN EmployeeGroup Division ON Division.ID = GL.DivisionID
WHERE GL.EntryDateTime BETWEEN  AND 
ORDER BY [Division Name], [GL DATE] DESC, JournalID, GLClassificationTypeID, [Account Name]

• Entered : 8/20/2009
• Version : 4.3+