Problem query for records for previous month

Last Post 23 May 2008 06:44 PM by SilentCodingOne. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SilentCodingOne
New Member
New Member

--
23 May 2008 12:36 PM
I'm working on project for school that involves building a query in a video store database. The query is suppose to pull the total number of movies rented the previous month. I can get it to work if I physically put in the dates. However, part of the requirements is to set it up so the date range is auto calculated. The following is the code I have


SELECT COUNT(RecordNumber) AS TotalRentalsForMonth FROM RentalHistory
WHERE TransactionDate BETWEEN (YEAR(getdate()), MONTH(getdate()), 1)
AND (YEAR(getdate()), MONTH(getdate())+1, 0)


I get the following error message when I try to run it:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.


Anyone have an idea where my mistake is within the date range
SilentCodingOne
New Member
New Member

--
23 May 2008 06:43 PM
Thanks I figured I was way off but I just couldn't figure it out. Sometimes a more experienced eye can help and do have it working and this is the corrected query

SELECT COUNT(RecordNumber) AS TotalRentalsForMonth FROM RentalHistory
WHERE TransactionDate BETWEEN DATEADD(ms,0,DATEADD(mm, DATEDIFF(m,0,GETDATE() )-1, 0))
AND DATEADD(ms,-5,DATEADD(mm, DATEDIFF(m,0,GETDATE() ), 0))
SilentCodingOne
New Member
New Member

--
23 May 2008 06:44 PM
Thanks for the tip gunneyk. The RecordNumber is the primary key and cannot be null. But you are right if that was not the case I would be better off with the asterik
You are not authorized to post a reply.

Acceptable Use Policy