Differences
This shows you the differences between two versions of the page.
control_sql_-_shows_amounts_from_undeposited_funds_by_division_for_each_deposit [2019/01/27 11:29] |
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 | ||
+ | || 3/20/07 4:50 PM || Company || Chase || $ | ||
+ | || 3/20/07 4:50 PM || Company || Chase || $ | ||
+ | || 3/20/07 4:50 PM || Company || Chase || $ | ||
+ | || 3/19/07 4:21 PM || Company || Chase || $ | ||
+ | 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" | ||
+ | |||
+ | |||
+ | |||
+ | declare @DateSTART datetime; | ||
+ | |||
+ | |||
+ | |||
+ | declare @DateEND datetime; | ||
+ | |||
+ | |||
+ | |||
+ | set @DateSTART | ||
+ | |||
+ | |||
+ | |||
+ | set @DateEND | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | select | ||
+ | |||
+ | |||
+ | |||
+ | Deposit.EntryDateTime as [Deposit Time], | ||
+ | |||
+ | |||
+ | |||
+ | (select DivisionName from EmployeeGroup where EmployeeGroup.ID | ||
+ | |||
+ | |||
+ | |||
+ | DepositAccount.AccountName as [Bank Account], | ||
+ | |||
+ | |||
+ | |||
+ | Deposit.Amount as [Deposit Amount], | ||
+ | |||
+ | |||
+ | |||
+ | (select Top 1 GLAccount.AccountName | ||
+ | |||
+ | |||
+ | |||
+ | from GL | ||
+ | |||
+ | |||
+ | |||
+ | join GLAccount on GL.GLAccountID | ||
+ | |||
+ | |||
+ | |||
+ | where GL.DepositJournalID | ||
+ | |||
+ | |||
+ | |||
+ | ) as [Undep Account], | ||
+ | |||
+ | |||
+ | |||
+ | (select SUM(Amount) | ||
+ | |||
+ | |||
+ | |||
+ | from GL Div1 | ||
+ | |||
+ | |||
+ | |||
+ | where Div1.DepositJournalID | ||
+ | |||
+ | |||
+ | |||
+ | and Div1.GLClassificationType | ||
+ | |||
+ | |||
+ | |||
+ | and coalesce(Div1.DivisionID, | ||
+ | |||
+ | |||
+ | |||
+ | ) as [Div1 Amount], | ||
+ | |||
+ | |||
+ | |||
+ | (select SUM(Amount) | ||
+ | |||
+ | |||
+ | |||
+ | from GL Div2 | ||
+ | |||
+ | |||
+ | |||
+ | where Div2.DepositJournalID | ||
+ | |||
+ | |||
+ | |||
+ | and Div2.GLClassificationType | ||
+ | |||
+ | |||
+ | |||
+ | and coalesce(Div2.DivisionID, | ||
+ | |||
+ | |||
+ | |||
+ | ) as [Div2 Amount], | ||
+ | |||
+ | |||
+ | |||
+ | (select SUM(Amount) | ||
+ | |||
+ | |||
+ | |||
+ | from GL Div3 | ||
+ | |||
+ | |||
+ | |||
+ | where Div3.DepositJournalID | ||
+ | |||
+ | |||
+ | |||
+ | and Div3.GLClassificationType | ||
+ | |||
+ | |||
+ | |||
+ | and coalesce(Div3.DivisionID, | ||
+ | |||
+ | |||
+ | |||
+ | ) as [Div3 Amount], | ||
+ | |||
+ | |||
+ | |||
+ | Deposit.JournalID as [Deposit JournalID], | ||
+ | |||
+ | |||
+ | |||
+ | Deposit.ID as [Deposit GLID] | ||
+ | |||
+ | |||
+ | |||
+ | from GL as Deposit | ||
+ | |||
+ | |||
+ | |||
+ | join GLAccount as DepositAccount on Deposit.GLAccountID | ||
+ | |||
+ | |||
+ | |||
+ | where Deposit.GLAccountID in (Select ID from GLAccount where GLClassificationType | ||
+ | |||
+ | |||
+ | |||
+ | and EntryDateTime between @DateSTART and @DateEND | ||
+ | |||
+ | |||
+ | |||
+ | and Deposit.Description like ' | ||
+ | |||
+ | |||
+ | |||
+ | order by [Deposit Time] desc | ||
+ | |||
+ | |||
+ | |||
+ | < | ||
+ | Version Information | ||
+ | * Entered : 8/24/2009 | ||
+ | * Version : Control 4.0+ | ||
+ | Related SQLs | ||