November 30, 2008 at 5:13 pm
For an appointment scheduling application, I am trying to write the SQL to figure out if a given date + recurring days falls within a specified range of dates.
Example: An appointment is scheduled for 12/5/08 2:00pm with recurrence each 7 days.
Table appts, apptDateTime = '12/5/08 2:00 pm', recurDays = 7.
How can I tell if this appointment will recur betweem 3/1/09 and 3/10/09?
To check if the appointment would fall on a single date, I can simply write:
select * from appts where recurDays > 0 and
DateDiff(dd, '3/1/09', apptDateTime) % recurDays = 0
I do not want to write a procedure or loop thru each date in the range.
Thanks in advance for any help.
November 30, 2008 at 6:28 pm
The following expression that uses integer division:
DATEDIFF(day, apptDateTime, @dt) / recurDays
will increment by one on the day of each recurring appointment (where @dt is a local variable of type datetime). Therefore, if the start and end of the date interval you which to query for recurring appointments are specified by the datetime local variables @dtStart and @dtEnd, the following query will do what (I think) you want.
SELECT * FROM appts
WHERE (recurDays > 0)
AND (DATEDIFF(day, apptDateTime, @dtEnd) >= 0)
AND (DATEDIFF(day, apptDateTime, @dtStart - 1) / recurDays < DATEDIFF(day, apptDateTime, @dtEnd) / recurDays)
Note that the WHERE clause includes the expression (@dtStart - 1) in order to pick up the case where an appointment occurs on the first day of the date interval. The query also checks that the last day of the query date interval is on or after the original appointment date in order to prevent returning a record where the date interval is entirely before the original appointment date.
December 1, 2008 at 5:31 am
Thank you, this is brilliant, works perfectly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply