Explanation of SQL
This SQL will give you a list of all Miscellaneous (non-order) Receipts “Income” for the specified time period.
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 @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
Version Information
- Entered : 09/09/2011
You could leave a comment if you were logged in.