Populate Appointment Dates Based on Availability

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Populate Appointment Dates Based on Availability

Author

Topic

TwoSignDesign
Starting Member

2 Posts

Posted - 2009-03-12 : 01:30:53

Hi All,

I'm sure you all hate the disclaimer of users being new to this forum, but I am! :) Please bare with me as I attempt to explain myself correctly.

I am in the middle of building a basic appointment application in C#, but I'm having trouble populating the appointment table.

I have a reference table (uMedical_ConsultantHours) that stores the general availability of consultants. I have provided a sample below.

Consultant Day TimeStart TimeFinish Available
1068 Monday 12/03/2009 8:00 12/03/2009 8:10 FALSE
1068 Monday 12/03/2009 8:10 12/03/2009 8:20 FALSE
1068 Monday 12/03/2009 8:20 12/03/2009 8:30 FALSE
1068 Monday 12/03/2009 8:30 12/03/2009 8:40 TRUE
1068 Monday 12/03/2009 8:40 12/03/2009 8:50 TRUE
1068 Monday 12/03/2009 8:50 12/03/2009 9:00 TRUE

I want to be able to run a stored procedure that then populates another table (uMedical_Appointments) with all of the dates for a given date range based on this reference table. For this example, let's say that date range is Monday, March 09, 2009 (09/03/2009) to Tuesday, March 17, 2009 (17/03/2009).

This is what I would expect to populate in the table uMedical_Appointments.

Consultant Date TimeStart TimeFinish Patient Other Available
1068 9/03/2009 12/03/2009 8:00 12/03/2009 8:10 FALSE
1068 9/03/2009 12/03/2009 8:10 12/03/2009 8:20 FALSE
1068 9/03/2009 12/03/2009 8:20 12/03/2009 8:30 FALSE
1068 9/03/2009 12/03/2009 8:30 12/03/2009 8:40 TRUE
1068 9/03/2009 12/03/2009 8:40 12/03/2009 8:50 TRUE
1068 9/03/2009 12/03/2009 8:50 12/03/2009 9:00 TRUE
1068 16/03/2009 12/03/2009 8:00 12/03/2009 8:10 FALSE
1068 16/03/2009 12/03/2009 8:10 12/03/2009 8:20 FALSE
1068 16/03/2009 12/03/2009 8:20 12/03/2009 8:30 FALSE
1068 16/03/2009 12/03/2009 8:30 12/03/2009 8:40 TRUE
1068 16/03/2009 12/03/2009 8:40 12/03/2009 8:50 TRUE
1068 16/03/2009 12/03/2009 8:50 12/03/2009 9:00 TRUE

The receptionist can then fill the patient column via the C# front end and change the daily availability of appointment times in case of an emergency, while still maintaining the general availability times for all consultants.

I have read about CTE functionality, but am unsure how I can apply it to this problem.

I appreciate your time.

Thanks,

Eloise

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 13:45:01

i think what you need is this


DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='2009-03-09',@EndDate='2009-03-17'

;With Date_CTE (Date,Day)
AS
(SELECT @StartDate,DATENAME(dw,@StartDate)
 UNION ALL
 SELECT DATEADD(dd,1, Date),DATENAME(dw,DATEADD(dd,1, Date))
 FROM Date_CTE
 WHERE DATEADD(dd,1, Date)<=@EndDate
)

INSERT INTO uMedical_Appointments
SELECT ch.Consultant,
c.Date,
ch.TimeStart,
ch.TimeFinish,
ch.Patient,
ch.Other,
ch.Available
FROM Date_CTE c
INNER JOIN uMedical_ConsultantHours ch
ON ch.Day=c.Day

TwoSignDesign
Starting Member

2 Posts

Posted - 2009-03-12 : 19:58:20

Thank you so much Visakh16! It's exactly what I needed.

For anyone else referring to this post, there is one correction I need to note. The fields 'Patient' and 'Other' do not come from table uMedical_ConsultantHouse ch. An empty string needs to be passed into the uMedical_Appointments table. The insert statement should there be as follows:

---->

INSERT INTO uMedical_Appointments
SELECT ch.Consultant,
c.Date,
ch.TimeStart,
ch.TimeFinish,
'' as Patient,
'' as Other,
ch.Available
FROM Date_CTE c
INNER JOIN uMedical_ConsultantHours ch
ON ch.Day=c.Day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources