This SQL will give you a list of all Miscellaneous (non-order) Receipts “Income” for the specified time period.

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

DECLARE @MinDate datetime;  -- Set the absolute earliest date you want to query
DECLARE @MaxDate datetime;  -- Set the absolute latest date you want to query
SET @MinDate   = CONVERT(DateTime, );
SET @MaxDate   = CONVERT(DateTime,   );
--SET @MinDate   = '1/26/2011';
--SET @MaxDate   = '12/26/2011 11:59:59 PM';
SELECT
A.CompanyName AS Company
,J.Description
,GL.EntryDateTime AS [DATE Posted]
,E.LastName + ', ' + FirstName AS [Posted BY]
,-(GL.Amount) AS Income
,GLA.AccountName AS [Income Account]
,J.Notes AS [Receipt Notes]
,(SELECT AccountName FROM Payment P WITH(NOLOCK)
LEFT JOIN PaymentAccount PA WITH(NOLOCK) ON P.PaymentAccountID = PA.ID
WHERE P.ID = GL.JournalID
) AS [Payment TYPE]
,(SELECT AccountName FROM Payment P WITH(NOLOCK)
LEFT JOIN GLAccount GLA1 WITH(NOLOCK) ON P.BankAccountID = GLA1.ID
WHERE P.ID = GL.JournalID
) AS [Bank Account]
FROM GL WITH(NOLOCK)
LEFT JOIN Account A WITH(NOLOCK) ON A.ID = GL.AccountID
LEFT JOIN Journal J WITH(NOLOCK) ON J.ID = GL.JournalID
LEFT JOIN GLAccount GLA WITH(NOLOCK) ON GLA.ID = GL.GLAccountID
LEFT JOIN Employee E WITH(NOLOCK) ON E.ID = J.EmployeeID
WHERE GL.JournalClassTypeID = 20038
AND GL.GLClassificationType BETWEEN 4000 AND 4999
AND GL.EntryDateTime BETWEEN @MinDate AND @MaxDate
ORDER BY GL.EntryDateTime, GL.JournalID
  • Entered : 09/09/2011
You could leave a comment if you were logged in.