A better way to handle repeating dates

I am a developer who works with MS SQL Server a lot, but I'm far from
an expert. I am revamping an appointment scheduling system that
allows for appointments to reoccur daily, weekly, monthly and yearly.

Currently, I am saving the appointment date as the initial appointment
date. Then when I want to check for appointments, my stored proc does
does a select on the appropriate records fitting certain critieria
(like only appointments for this doctor, at this location, etc). Once
I have these records I cycle through them calling the DateAdd() and
DateDiff() functions to see if the appointment is reoccuring during
the dates I'm looking for.

Here's is a mock up of what I'm doing. I know cursors are a huge hit
performance-wise (especially how they are used in this scenario) and
want to get a way from this, but I can't figure out how to get
reoccuring appointments to work. Any help is appreciated. Thanks.

sp_GetAppointments(@.StartDate, @.EndDate)

set @.DateToCheck = @.StartApptDate
while @.DateToCheck <= @.EndApptDate
begin
--Start a cursor
DECLARE RepeatCursor CURSOR
FORWARD_ONLY STATIC FOR

select ApptDate from ApptTable where DoctorID = 1 and

--Check if it repeats daily
((repeat = 1 and
DateAdd(d,DateDiff(d,ApptDate,@.DateToCheck),ApptDa te) =
@.DateToCheck
and DateDiff(d,ApptDate,@.DateToCheck) >0)

--Check if it repeats weekly
or (repeat = 2 and
DateAdd(wk,DateDiff(wk,ApptDate,@.DateToCheck),Appt Date) =
@.DateToCheck
and DateDiff(d,ApptDate,@.DateToCheck) >0)

CLOSE RepeatCursor
DEALLOCATE RepeatCursor

set @.DateToCheck = DateAdd(d,1,@.DateToCheck)
end"Dean" <daudirsch@.hotmail.com> wrote in message
news:1f9c615a.0407140934.33853b8d@.posting.google.c om...
> I am a developer who works with MS SQL Server a lot, but I'm far from
> an expert. I am revamping an appointment scheduling system that
> allows for appointments to reoccur daily, weekly, monthly and yearly.
> Currently, I am saving the appointment date as the initial appointment
> date. Then when I want to check for appointments, my stored proc does
> does a select on the appropriate records fitting certain critieria
> (like only appointments for this doctor, at this location, etc). Once
> I have these records I cycle through them calling the DateAdd() and
> DateDiff() functions to see if the appointment is reoccuring during
> the dates I'm looking for.
> Here's is a mock up of what I'm doing. I know cursors are a huge hit
> performance-wise (especially how they are used in this scenario) and
> want to get a way from this, but I can't figure out how to get
> reoccuring appointments to work. Any help is appreciated. Thanks.
> sp_GetAppointments(@.StartDate, @.EndDate)
> set @.DateToCheck = @.StartApptDate
> while @.DateToCheck <= @.EndApptDate
> begin
> --Start a cursor
> DECLARE RepeatCursor CURSOR
> FORWARD_ONLY STATIC FOR
> select ApptDate from ApptTable where DoctorID = 1 and
> --Check if it repeats daily
> ((repeat = 1 and
> DateAdd(d,DateDiff(d,ApptDate,@.DateToCheck),ApptDa te) =
> @.DateToCheck
> and DateDiff(d,ApptDate,@.DateToCheck) >0)
> --Check if it repeats weekly
> or (repeat = 2 and
> DateAdd(wk,DateDiff(wk,ApptDate,@.DateToCheck),Appt Date) =
> @.DateToCheck
> and DateDiff(d,ApptDate,@.DateToCheck) >0)
> CLOSE RepeatCursor
> DEALLOCATE RepeatCursor
> set @.DateToCheck = DateAdd(d,1,@.DateToCheck)
> end

I'm not sure that I see how you identify an appointment from the information
above, since it seems that you're only looking at dates. If the doctor has
an appointment today, and one in a week, how does he know if they're related
or unrelated? And what about the time of day?

In any case, some standard advice would be to remove the sp_ prefix, which
is reserved for system stored procedures, and to investigate using a
calendar table to help you with date-related queries. For more specific
advice, you will have to give more details, and someone may be able to
suggest something - CREATE TABLE statements for the tables you're looking at
(perhaps simplified), INSERT statements for sample data, and then the output
you would like to have. But if your business requirements are complex, it
may be tricky to resolve in a newsgroup.

Simon|||As Simon has suggested, some more info would help us understand your
requirements better.

Here's a simplified example of how you could generate repeating appointments
without a cursor.

CREATE TABLE Appointments (doctorid INTEGER NOT NULL, start_dt DATETIME,
end_dt DATETIME NOT NULL, CHECK (start_dt<end_dt), repeat INTEGER NOT NULL
DEFAULT 1 CHECK (repeat>0), repeat_days INTEGER NOT NULL DEFAULT 0,
repeat_months INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (doctorid, start_dt))

The Repeat column defines how many times an appointment occurs and the
Repeat_Days / Repeat_Months columns define the interval either in months or
days.

Here are some sample appointments:

INSERT INTO Appointments VALUES /* Single appointment */
(1,'2004-01-15T10:00:00.000','2004-01-15T10:30:00.000',1,0,0)

INSERT INTO Appointments VALUES /* Weekly for 5 weeks */
(2,'2004-01-16T12:30:00.000','2004-01-16T13:30:00.000',5,7,0)

INSERT INTO Appointments VALUES /* Monthly for 6 months */
(3,'2004-02-01T14:30:00.000','2004-02-01T15:30:00.000',6,0,1)

Here's the query to generate the repeating appointments (you need to create
an auxiliary Numbers table first:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp)

SELECT doctorid,
DATEADD(MONTH,(N.number-1)*repeat_months,
DATEADD(DAY,(N.number-1)*repeat_days, A.start_dt)),
DATEADD(MONTH,(N.number-1)*repeat_months,
DATEADD(DAY,(N.number-1)*repeat_days, A.end_dt))
FROM Appointments AS A
JOIN Numbers AS N
ON N.number BETWEEN 1 AND A.repeat

Whether it then makes sense to insert this result into another table or just
extrapolate the appointments with this query as needed really depends on
your business requirements.

Hope this helps.

--
David Portas
SQL Server MVP
--|||>> I am revamping an appointment scheduling system that allows for
appointments to reoccur daily, weekly, monthly and yearly. <<

The first problem you have is your mental model. Look at the words in
your specs!

>> Currently, I am saving the appointment date as the initial [sic]
appointment date. Then when I want to check for appointments, my
stored proc does does a select on the appropriate records [sic]
fitting certain critieria ... Once I have these records [sic] I cycle
[sic] through them calling the DateAdd() and DateDiff() functions
[sic] to see if the appointment is reoccuring during the dates I'm
looking for. <<

Rows are not records. Cycles (loops) are procedural. We prefer data
that holds all the facts over functions and computations that build
them on the fly.

When you make the appointment, it is not one appointment; you are
making a set of appointments ("Well, Mr. Celko, we'll see you here
every other week until you die, or your insurance gives out for the
next five years!").

Use a calendar table for the schedules so that nobody gets a check up
on Christmas and New Years. You can also predict when a doctor is
going to be overloaded in advance and prevent it. Pull out a base
schedule from the calendar table, add the client and doctor, and then
modify it as you need to later in time ("I'm too sick to come to
chemotherapy today!"). This ad hoc change is the way this is really
done.

Worse case? A daily visit for 10 years in advance costs you (365.2422
*10 rows) = 3653 rows of (datetime, patient, doctor) data in the
appointment table. It lets me replace one doctor for another in
advance, too.|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<X5adnZu_EoBOEGjdRVn-gg@.giganews.com>...
> As Simon has suggested, some more info would help us understand your
> requirements better.
> Here's a simplified example of how you could generate repeating appointments
> without a cursor.
> CREATE TABLE Appointments (doctorid INTEGER NOT NULL, start_dt DATETIME,
> end_dt DATETIME NOT NULL, CHECK (start_dt<end_dt), repeat INTEGER NOT NULL
> DEFAULT 1 CHECK (repeat>0), repeat_days INTEGER NOT NULL DEFAULT 0,
> repeat_months INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (doctorid, start_dt))
> The Repeat column defines how many times an appointment occurs and the
> Repeat_Days / Repeat_Months columns define the interval either in months or
> days.
> Here are some sample appointments:
> INSERT INTO Appointments VALUES /* Single appointment */
> (1,'2004-01-15T10:00:00.000','2004-01-15T10:30:00.000',1,0,0)
> INSERT INTO Appointments VALUES /* Weekly for 5 weeks */
> (2,'2004-01-16T12:30:00.000','2004-01-16T13:30:00.000',5,7,0)
> INSERT INTO Appointments VALUES /* Monthly for 6 months */
> (3,'2004-02-01T14:30:00.000','2004-02-01T15:30:00.000',6,0,1)
> Here's the query to generate the repeating appointments (you need to create
> an auxiliary Numbers table first:
> http://www.bizdatasolutions.com/tsql/tblnumbers.asp)
> SELECT doctorid,
> DATEADD(MONTH,(N.number-1)*repeat_months,
> DATEADD(DAY,(N.number-1)*repeat_days, A.start_dt)),
> DATEADD(MONTH,(N.number-1)*repeat_months,
> DATEADD(DAY,(N.number-1)*repeat_days, A.end_dt))
> FROM Appointments AS A
> JOIN Numbers AS N
> ON N.number BETWEEN 1 AND A.repeat
> Whether it then makes sense to insert this result into another table or just
> extrapolate the appointments with this query as needed really depends on
> your business requirements.
> Hope this helps.

Thanks to all for the assistance, it really helped.