"Dean" <daudirsch@hotmail.com> wrote in message
news:1f9c615a.0407140934.33853b8d@posting.google.com...
> 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),ApptDate) =
> @DateToCheck
> and DateDiff(d,ApptDate,@DateToCheck) >0)
>
> --Check if it repeats weekly
> or (repeat = 2 and
> DateAdd(wk,DateDiff(wk,ApptDate,@DateToCheck),ApptDate) =
> @DateToCheck
> and DateDiff(d,ApptDate,@DateToCheck) >0)
>
> CLOSE RepeatCursor
> DEALLOCATE RepeatCursor
>
> set @DateToCheck = DateAdd(d,1,@DateToCheck)
> end

>> I am revamping an appointment scheduling system that allows for

>> Currently, I am saving the appointment date as the initial [sic]

"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.