Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_shows_amounts_from_undeposited_funds_by_division_for_each_deposit [2019/01/27 11:29] (current)
Line 1: Line 1:
 + 
 +
 +
 +
 +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 
 +
 +
 +
 +[[code_formatsql|code format"​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