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
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]
Version Information
- Entered : 8/3/2009
- Version : 4.3+
You could leave a comment if you were logged in.