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.
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]
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)
)
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].[Appo
WHERE a1.PhysicianId = @PhysicianId
AND a1.EndTime > GETDATE()
AND NOT EXISTS (SELECT BegTime
FROM [CMS].[Appointments].[Appo
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.
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].[Appo
WHERE a1.PhysicianId = @PhysicianId
AND a1.EndTime > GETDATE()
AND NOT EXISTS (SELECT BegTime
FROM [CMS].[Appointments].[Appo
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.
Thanks for using Experts Exchange.
Please provide your email to receive a sample view!
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.