Previous Month SQL Query with GETDATE within VWD 2005

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?
accessloansAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
for current months use this

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 CaseProcessing.StatusDate > = CONVERT(varchar(6), Getdate(),112) +'01'
AND (CaseDetails.LoanTypeID = 8) AND (Status.STATUS = 'COM') OR (CaseDetails.LoanTypeID = 9) AND (Status.STATUS = 'COM')
GROUP BY Lender.Company
ORDER BY Lender.Company


Inorder to include the previous month's 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 CaseProcessing.StatusDate > = DATEADD(m, -1,CONVERT(varchar(6), Getdate(),112) +'01' )
AND (CaseDetails.LoanTypeID = 8) AND (Status.STATUS = 'COM') OR (CaseDetails.LoanTypeID = 9) AND (Status.STATUS = 'COM')
GROUP BY Lender.Company
ORDER BY Lender.Company
0
 
Scott PletcherSenior DBACommented:
One mild correction: for the previous month (only), you need to specify and end date as well:

WHERE CaseProcessing.StatusDate > = DATEADD(m, -1,CONVERT(char(6), Getdate(), 112) + '01')
AND CaseProcessing.StatusDate < CONVERT(char(6), Getdate(),  112) + '01'
AND ...
0
 
accessloansAuthor Commented:
Thanks for the help. I got it working. My query is -

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     (CaseDetails.LoanTypeID = 8) AND (Status.STATUS = 'COM') AND (CaseProcessing.StatusDate >= DATEADD(m, - 1, CONVERT(char(6), GETDATE(), 112)
                      + '01')) AND (CaseProcessing.StatusDate < CONVERT(char(6), GETDATE(), 112) + '01') OR
                      (CaseDetails.LoanTypeID = 9) AND (Status.STATUS = 'COM') AND (CaseProcessing.StatusDate >= DATEADD(m, - 1, CONVERT(char(6), GETDATE(), 112)
                      + '01')) AND (CaseProcessing.StatusDate < CONVERT(char(6), GETDATE(), 112) + '01')
GROUP BY Lender.Company
ORDER BY Lender.Company

AS you can see from my query it counts how many cases a lender had last month. If one of the lenders didnt have a case the lender isnt displayed. If the lender didnt have a case how do i get it to display 0 for a null value
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
accessloansAuthor Commented:
should i create another question for my additional help?
0
 
accessloansAuthor Commented:
I will add another question for above about null values.

Can you explain how your DATEADD works as it is baffling me with the convert, char(6) etc. Just so i have an understanding of it instead of just taking the answer.
0
 
Scott PletcherSenior DBACommented:
The CONVERT to a format of 112 gives a result of YYYYMMDD.  So, taking the first six only gives YYYYMM.  Putting '01' on there gives the first of the month, since YYYYMMDD is a valid format for an input date.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.