This SQL provides a table of each deposit made in Control and all of the payments that went into them.

None. This is a selection query and no data is modified in the running of it.

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