I am using the following query to find the current months records -
SELECT Lender.Company, COUNT(Status.STATUS) AS [Completed Cases] FROM CaseDetails INNER JOIN CaseProcessing ON CaseDetails.CaseID = CaseProcessing.CaseID INNER JOIN Status ON CaseProcessing.StatusID = Status.ID INNER JOIN CaseUnderWriting ON CaseDetails.CaseID = CaseUnderWriting.CaseID INNER JOIN Lender ON CaseUnderWriting.LenderID = Lender.ID
WHERE (MONTH(CaseProcessing.StatusDate) = MONTH(GETDATE())) AND (YEAR(CaseProcessing.StatusDate) = YEAR(GETDATE())) AND (CaseDetails.LoanTypeID = 8) AND (Status.STATUS = 'COM') OR (CaseDetails.LoanTypeID = 9) AND (Status.STATUS = 'COM')
GROUP BY Lender.Company
ORDER BY Lender.Company
I am trying to change it so that it extracts the previous months records but cant get it to work.
also after the turn of the year will the query work?