Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How I can get the first timeslot?
Posted on 2009-07-08
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]
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4
5 Comments
Active today
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)
Active today
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].[Appointmen
WHERE a1.PhysicianId = @PhysicianId AND
a1.BegTime > GETDATE()
SELECT MIN(a1.BegTime) FirstAppointment
FROM [Appointments].[Appointmen
WHERE a1.PhysicianId = @PhysicianId AND
a1.BegTime > GETDATE()
Active today
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 8 hours left to enroll
626 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.