Explanation of SQL
This SQL provides a listing of the deposits made, what source (undeposited funds account) they came from, and a breakdown of the amounts for up to 3 divisions.
To make this work you will need to correct the division ID in the 2nd and 3rd query to match your divisions.
Sample Output
| Deposit Time | Division Name | Bank Account | Deposit Amount | Undep Account | Div1 Amount | Div2 Amount | Div3 Amount | Deposit JournalID | Deposit GLID | ||||||||||
| 3/20/07 4:50 PM | Company | Chase | $ 104.58 | 1002 Undeposited AmEx | $ 104.58 | NULL | NULL | 1795689 | 269554 | ||||||||||
| 3/20/07 4:50 PM | Company | Chase | $ 360.00 | 1003 Undeposited Cash-Checks | $ 360.00 | NULL | NULL | 1795690 | 269556 | ||||||||||
| 3/20/07 4:50 PM | Company | Chase | $ 674.00 | 1006 Undeposited MC-Visa | $ 674.00 | NULL | NULL | 1795691 | 269558 | ||||||||||
| 3/19/07 4:21 PM | Company | Chase | $ 630.00 | 1002 Undeposited AmEx | $ 630.00 | NULL | NULL | 1794823 | 269526 |
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
declare @DateSTART datetime;
declare @DateEND datetime;
set @DateSTART '8/17/2009'; –
set @DateEND '8/24/2009'; –
select
Deposit.EntryDateTime as [Deposit Time],
(select DivisionName from EmployeeGroup where EmployeeGroup.ID Deposit.DivisionID) as [Division Name],
DepositAccount.AccountName as [Bank Account],
Deposit.Amount as [Deposit Amount],
(select Top 1 GLAccount.AccountName
from GL
join GLAccount on GL.GLAccountID GLAccount.ID
where GL.DepositJournalID Deposit.JournalID
) as [Undep Account],
(select SUM(Amount)
from GL Div1
where Div1.DepositJournalID Deposit.JournalID
and Div1.GLClassificationType 1007
and coalesce(Div1.DivisionID, 10) 10
) as [Div1 Amount],
(select SUM(Amount)
from GL Div2
where Div2.DepositJournalID Deposit.JournalID
and Div2.GLClassificationType 1007
and coalesce(Div2.DivisionID, 10) 20
) as [Div2 Amount],
(select SUM(Amount)
from GL Div3
where Div3.DepositJournalID Deposit.JournalID
and Div3.GLClassificationType 1007
and coalesce(Div3.DivisionID, 10) 30
) as [Div3 Amount],
Deposit.JournalID as [Deposit JournalID],
Deposit.ID as [Deposit GLID]
from GL as Deposit
join GLAccount as DepositAccount on Deposit.GLAccountID DepositAccount.ID
where Deposit.GLAccountID in (Select ID from GLAccount where GLClassificationType 1000 and IsActive 1)
and EntryDateTime between @DateSTART and @DateEND
and Deposit.Description like 'Deposit From%'
order by [Deposit Time] desc
<code> Version Information
- Entered : 8/24/2009
- Version : Control 4.0+
Related SQLs