Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== ====== ===== 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+