Solved

How I can get the first timeslot?

Posted on 2009-07-08
5
353 Views
Last Modified: 2012-05-07
I have created a calendar, time slots and appointment tables to try to manage appointments.

The Calendar table has been filled with the dates from 20000101' to '20291231'. The Timeslot table has been filled with a time slots of ten minutes for every date in the Calendar table (144 slots for each calendar date).

I need to know how I can get the first available appointment for a given duration and physicianId?

Thank you.

Calendar Table:

CREATE TABLE [Calendars].[Calendar](

	[CalDate] [date] NOT NULL,

	[isWeekday] [bit] NULL,

	[isHoliday] [bit] NULL,

	[Y] [smallint] NULL,

	[FY] [smallint] NULL,

	[Q] [tinyint] NULL,

	[M] [tinyint] NULL,

	[D] [tinyint] NULL,

	[DW] [tinyint] NULL,

	[monthname] [varchar](9) NULL,

	[dayname] [varchar](9) NULL,

	[W] [tinyint] NULL,

	[UTCOffset] [tinyint] NULL,

	[HolidayDescription] [varchar](32) NULL,

PRIMARY KEY CLUSTERED 

(

	[CalDate] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]
 

TimeSlots Table:

CREATE TABLE [Calendars].[TimeSlot](

	[SlotId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

	[CalDate] [date] NOT NULL,

	[SlotTime] [datetime] NOT NULL,

 CONSTRAINT [PK_TimeSlots] PRIMARY KEY CLUSTERED 

(

	[SlotId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]
 

Appointment Table:

CREATE TABLE [Appointments].[Appointment](

	[AppointmentId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

	[AppointmentDate] [date] NOT NULL,

	[BegTime] [datetime] NOT NULL,

	[EndTime] [datetime] NOT NULL,

	[Duration] [int] NOT NULL,

	[PhysicianId] [int] NOT NULL,

	[FileId] [int] NOT NULL,

	[RoomId] [int] NULL,

	[ProcedureId] [int] NOT NULL,

	[VisitStatusId] [int] NOT NULL,

	[ConfirmId] [int] NOT NULL,

	[IsArrived] [bit] NOT NULL,

	[IsRowDeleted] [bit] NOT NULL,

 CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED 

(

	[AppointmentId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

Open in new window

0
Comment
Question by:Samijf
  • 4
5 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 24813334
The following code shows how to obtain the nearest gap. If you need some time difference between now and the nearest gap starting time then you have to change the GETDATE() by e.g. DATEADD(minute, 30, GETDATE()).

DECLARE @PhysicianId int

DECLARE @DurationInMinutes int

SET @PhysicianId = <>

SET @DurationInMinutes = <>
 

SELECT ISNULL(MIN(a1.EndTime), GETDATE()) FirstGapStart 

  FROM [Appointments].[Appointment] a1 

 WHERE a1.PhysicianId = @PhysicianId AND 

       a1.EndTime > GETDATE() AND 

       NOT EXISTS (SELECT BegTime 

                     FROM [Appointments].[Appointment] 

                    WHERE PhysicianId = @PhysicianId AND 

                          BegTime < DATEADD(minute, @DurationInMinutes, a1.EndTime) AND 

                          BegTime > a1.EndTime)

Open in new window

0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 500 total points
ID: 24813383
But you are asking for the first available appointment not for the gap to create new appointment, so it is even easier:

SELECT MIN(a1.BegTime) FirstAppointment
  FROM [Appointments].[Appointment] a1
 WHERE a1.PhysicianId = @PhysicianId AND
       a1.BegTime > GETDATE()  

0
 
LVL 41

Expert Comment

by:pcelba
ID: 24813466
To look for the first available gaps is more probable. If you need to round the time of the first available gap to your Slots then you may simply use above gap calcuation as subquery for slot time selection:


DECLARE @PhysicianId int

DECLARE @DurationInMinutes int

SET @PhysicianId = <>

SET @DurationInMinutes = <>
 

SELECT MIN(SlotTime) SlotGapStart 

  FROM [Calendars].[TimeSlot] 

 WHERE SlotTime >= (

       SELECT ISNULL(MIN(a1.EndTime), GETDATE()) FirstGapStart 

         FROM [Appointments].[Appointment] a1 

        WHERE a1.PhysicianId = @PhysicianId AND 

              a1.EndTime > GETDATE() AND 

              NOT EXISTS (SELECT BegTime 

                            FROM [Appointments].[Appointment] 

                           WHERE PhysicianId = @PhysicianId AND 

                                 BegTime < DATEADD(minute, @DurationInMinutes, a1.EndTime) AND 

                                 BegTime > a1.EndTime)

       )

Open in new window

0
 

Author Comment

by:Samijf
ID: 25138282
Hi,
I'm sorry for my late replay but I was really busy with other things that my priorities force me to do.

I did try all of the select statement giving by you and the first one work with me but I need to conceder other issues.

The following is the SQL stamen I use which given to me by "pcelba".

DECLARE @PhysicianId int = 1
DECLARE @DurationInMinutes int = 10

SELECT ISNULL(MIN(a1.EndTime), GETDATE()) FirstGapStart
  FROM [CMS].[Appointments].[Appointment] a1
 WHERE a1.PhysicianId = @PhysicianId
   AND a1.EndTime > GETDATE()
   AND NOT EXISTS (SELECT BegTime
                     FROM [CMS].[Appointments].[Appointment]
                    WHERE PhysicianId = @PhysicianId
                      AND BegTime < DATEADD(minute, @DurationInMinutes, a1.EndTime)
                      AND BegTime > a1.EndTime)

It works fine.

But I need also to return the first three available appointments and they must be within the physician working hours.

For example Physician No 1 works time:
From 10:00 AM To 01:00 PM
FROM 04:00 PM  To  10:00 PM
 
Please I need your help.

Thank you.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 25138351
To implement physician working hours you need to define/disclose table which will contain these data.

The best thing you coud do now is to close this question and start a new one. It will fullfil EE rules and ensure fast answer.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now