I am attempting to pull back records that have a birthday in a particular month, of a certain membership type, who are active AND have checked into the facility within the last 30 days. But I am having difficulty with the 30 logic. Every way that I attempt to calculate the date the query in MS SQL errors out.
I keep getting a syntax error related to the date calcualtion.
MEMBERS.scancode, MEMBERS.lname, MEMBERS.fname, MEMBERS.dob,
MEMBERS.status, MEMBERTYPES.description, MEMBERS.mtypeid,
INNER JOIN SITES ON MEMBERS.siteid = SITES.siteid
INNER JOIN MEMBERTYPES ON MEMBERS.mtypeid = MEMBERTYPES.mtypeid
WHERE (MEMBERS.siteid = @rvSite)
AND (MEMBERS.status = 'A')
AND (MONTH(MEMBERS.dob) = @rvMonth)
AND (MEMBERS.relationship = 'M')
AND (MEMBERS.mtypeid IN (1, 10, 12, 22, 28, 32, 33, 34, 35, 36, 40, 48))
AND (DATEDIFF(MEMBERS.lastvisit, GETDATE()) <= 30)
DATEDIFF function requires 3 arguments
datepart , startdate , enddate. You were missing the first argument (datepart) in the query. Change it to
DATEDIFF(DD,MEMBERS.lastvisit, GETDATE()) <= 30