Differences

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

Link to this comparison view

control_sql_-_shows_payments_that_went_into_each_deposit [2019/01/27 11:29] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +This SQL provides a table of each deposit made in Control and all of the payments that went into them.
 +
 +
 +
 +===== 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 sql>
 +declare @StartDate DateTime;
 +declare @EndDate DateTime;
 +declare @ShowOnlyAnomolies char(1);
 + 
 +--SET @StartDate = ;
 +--SET @EndDate ​  = ;
 +--SET @ShowOnlyAnomolies = ;
 +SET @StartDate = '​1/​1/​2009';​
 +SET @EndDate = '​1/​1/​2010';​
 +SET @ShowOnlyAnomolies = '​N';​
 + 
 +SELECT *
 +FROM
 +(
 + SELECT ​
 +    ​(SELECT DivisionName FROM EmployeeGroup WHERE ID = DJ.DivisionID) AS [Deposit Division],
 +    ​DJ.StartDateTime AS [Deposit Date], ​
 +    ​P.DepositJournalID AS [Deposit ID],
 +    ​(SELECT top 1 GLAccount.AccountName ​
 + FROM GL JOIN GLAccount ON GL.GLAccountID = GLAccount.ID
 + WHERE JournalID = P.DepositJournalID AND GLAccountID ​ P.BankAccountID) AS [Deposit GL Account],
 +  
 +    ​DJ.SummaryAmount AS [Deposit Amount],
 +    
 +    ​(SELECT Sum(ThisJournal.DetailAmount)
 + FROM Payment as ThisPayment ​
 + JOIN Journal as ThisJournal on ThisPayment.ID = ThisJournal.ID
 + WHERE ThisPayment.DepositJournalID = P.DepositJournalID) AS [Journal Deposit Sum],
 +  
 +    ​(SELECT DivisionName FROM EmployeeGroup WHERE ID = J.DivisionID) AS [Payment Division],
 +    ​A.CompanyName AS [Company Name],
 +    ​TH.OrderNumber AS [ORDER Number],
 +    ​TH.Description AS [ORDER Description],​
 +    ​J.StartDateTime AS [Payment Date], ​
 +    ​(SELECT AccountName FROM PaymentAccount WHERE ID = P.PaymentAccountID) AS [Payment Method], ​
 +    ​J.DetailAmount AS [Payment Amount], ​
 +    ​(SELECT AccountName FROM GLAccount WHERE ID = P.BankAccountID) AS [Payment Account],
 +    ​P.TenderType, ​
 +    ​(SELECT LastName + ', ' + FirstName FROM Employee WHERE ID = J.EmployeeID) AS PaymentEmployee,​
 +  
 +    ​J.IsVoided AS [Was Voided],
 +    ​J.VoidedDateTime AS [Voided Date],
 +  
 +    P.ID AS PaymentID, ​  
 +    ​P.BankAccountID AS PaymentGLAccountID, ​
 +    ​P.PaymentAccountID,​
 +    ​(SELECT top 1 GLAccountID ​
 + FROM GL 
 + WHERE JournalID = P.DepositJournalID AND GLAccountID ​ P.BankAccountID) AS DepositGLAccountID,​
 +    ​J.DivisionID AS PaymentDivisionID,​
 +    ​DJ.DivisionID AS DepositDivisionID,​
 +    ​J.TransactionID AS TransHeaderID,​
 +    ​J.EmployeeID AS EmployeeID,
 +    ​J.AccountID AS AccountID
 +  
 +  
 + FROM Payment P
 + JOIN Journal J ON P.ID = J.ID 
 + LEFT JOIN Journal DJ ON DJ.ID = P.DepositJournalID
 + LEFT JOIN TransHeader TH ON J.TransactionID = TH.ID
 + LEFT JOIN Account A ON J.AccountID = A.ID
 +  
 + WHERE ​
 +   DJ.CompletedDateTime BETWEEN @StartDate AND @EndDate
 +   AND P.DepositJournalID IS NOT NULL
 +   AND J.IsSummary = 0
 +) FinalTable
 +WHERE @ShowOnlyAnomolies='​N'​
 +  or ([Deposit Division] ​ [Payment Division])
 +  or ([Deposit Amount] ​ [Journal Deposit Sum])
 + 
 +ORDER BY [Deposit Date], [Payment Date], [Payment Method], [Payment Account]
 +</​code>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 8/3/2009
 +  * Version : 4.3+
 +
 +
 +