How I can get the first timeslot?

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

SamiDeveloper Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
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
pcelbaCommented:
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
SamiDeveloper Author Commented:
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
pcelbaCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.