• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1509
  • Last Modified:

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?
0
accessloans
Asked:
accessloans
  • 3
  • 2
2 Solutions
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now