473,381 Members | 1,497 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,381 software developers and data experts.

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
Jul 20 '05 #1
4 4349

"Dean" <da*******@hotmail.com> wrote in message
news:1f**************************@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
Jul 20 '05 #2
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
--
Jul 20 '05 #3
>> 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.
Jul 20 '05 #4
"David Portas" <RE****************************@acm.org> wrote in message news:<X5********************@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.
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jennie Friesen | last post by:
Hello-- I would like to display a different line of text (32 different messages) on refresh, BUT with no repeats. The script I am currently using is: <script language=javascript...
8
by: brian.digipimp | last post by:
I turned this in for my programming fundamentals class for our second exam. I am a c++ newb, this is my first class I've taken. I got a good grade on this project I'm just wondering if there is a...
1
by: Uncle Mike | last post by:
Hi all, I can't tell if I'm just being a spaccer, or whether this is tricky. Here goes. (Please note - this is a dummy example, for ease of explaining.) So, I have a table with three...
4
by: Rob Johnson | last post by:
I have an ASP.Net calendar feature which allows users to add events and configure whether or not they repeat at various frequencies (i.e, daily, weekly, monthly, Sat/Sun, etc.). What I'm looking...
1
by: Tommy | last post by:
The problem is how to achieve the transformation as below: The source xml contains tons of repeating structure like below, each item node contains a person element and a insurance element that...
5
by: Robert W. | last post by:
My app runs perfectly when run in Canada or the U.S. But others are experiencing problems. So I switched my computer to the UK culture and immediately saw a problem. This line was failing: ...
2
by: serge | last post by:
/* Subject: How best to use BETWEEN Begin and End Dates to find out if an employee was/is member of any group for a certain date range? You can copy/paste this whole post in SQL Query Analyzer...
2
by: needin4mation | last post by:
Hi, I have a database that requires dates in this format: 30-sep-2006 The ODBC driver does not convert. In 1.1 I either parsed the date and put it in the right format or used a validator on...
10
by: rcamarda | last post by:
I have created a table that contains buckets to hold activitives of enrollment for each of our admissions officer for each day of an enrollment session. I have an UPDATE that builds rolling totals...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.