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