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+
 +
 +
 +