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.
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]