CODEWITHSUNDEEP
sqlsql-server
Andy P.
Andy P.

Reputation: 39

SQL Server Find Available Time Slots

I'm new to SQL and I'm trying to figure out how to get available appointment slot based on the attached example data set. The date portion of it can be ignored, only the time matters.

Thanks you in advance.

Current Data Set

Need help writing SQL query to provide actual available slots:

Result the query should return

Sample Data:

IF OBJECT_ID('tempdb..#MyData') IS NOT NULL DROP TABLE #MyData
CREATE TABLE #MyData (
ScheduleTimesId INT,
ScheduleId INT,
FacilityId INT,
ApptTypeId INT,
SlotTimeStart DATETIME, 
SlotTimeStop DATETIME, 
ApptTimeStart DATETIME, 
ApptTimeStop DATETIME, 
ApptType VARCHAR(30)
)
INSERT INTO #MyData
VALUES
('75999','2124','499','161','1900-01-01 09:00:00.000','1900-01-01 09:20:00.000','1900-01-01 09:00:00.000','1900-01-01 10:00:00.000','Mandatory Dept Meeting')
,('76000','2124','499',NULL,'1900-01-01 09:20:00.000','1900-01-01 09:40:00.000',NULL,NULL,NULL)
,('76001','2124','499',NULL,'1900-01-01 09:40:00.000','1900-01-01 10:00:00.000',NULL,NULL,NULL)
,('76002','2124','499','167','1900-01-01 10:00:00.000','1900-01-01 10:20:00.000','1900-01-01 10:00:00.000','1900-01-01 11:00:00.000','Admin Time')
,('76003','2124','499',NULL,'1900-01-01 10:20:00.000','1900-01-01 10:40:00.000',NULL,NULL,NULL)
,('76004','2124','499',NULL,'1900-01-01 10:40:00.000','1900-01-01 11:00:00.000',NULL,NULL,NULL)
,('76005','2124','499','167','1900-01-01 11:00:00.000','1900-01-01 11:20:00.000','1900-01-01 11:00:00.000','1900-01-01 12:00:00.000','Admin Time')
,('76006','2124','499',NULL,'1900-01-01 11:20:00.000','1900-01-01 11:40:00.000',NULL,NULL,NULL)
,('76007','2124','499',NULL,'1900-01-01 11:40:00.000','1900-01-01 12:00:00.000',NULL,NULL,NULL)
,('76008','2124','499','168','1900-01-01 12:00:00.000','1900-01-01 12:20:00.000','1900-01-01 12:00:00.000','1900-01-01 13:00:00.000','LUNCH - 60 MIN')
,('76009','2124','499',NULL,'1900-01-01 12:20:00.000','1900-01-01 12:40:00.000',NULL,NULL,NULL)
,('76010','2124','499',NULL,'1900-01-01 12:40:00.000','1900-01-01 13:00:00.000',NULL,NULL,NULL)
,('76011','2124','499','160','1900-01-01 13:00:00.000','1900-01-01 13:20:00.000','1900-01-01 13:00:00.000','1900-01-01 15:00:00.000','Team Meeting')
,('76012','2124','499',NULL,'1900-01-01 13:20:00.000','1900-01-01 13:40:00.000',NULL,NULL,NULL)
,('76013','2124','499',NULL,'1900-01-01 13:40:00.000','1900-01-01 14:00:00.000',NULL,NULL,NULL)
,('76014','2124','499',NULL,'1900-01-01 14:00:00.000','1900-01-01 14:20:00.000',NULL,NULL,NULL)
,('76015','2124','499',NULL,'1900-01-01 14:20:00.000','1900-01-01 14:40:00.000',NULL,NULL,NULL)
,('76016','2124','499',NULL,'1900-01-01 14:40:00.000','1900-01-01 15:00:00.000',NULL,NULL,NULL)
,('76017','2124','499',NULL,'1900-01-01 15:00:00.000','1900-01-01 15:20:00.000',NULL,NULL,NULL)
,('76018','2124','499','191','1900-01-01 15:20:00.000','1900-01-01 15:40:00.000','1900-01-01 15:20:00.000','1900-01-01 15:40:00.000','MD Return 20')
,('76019','2124','499','191','1900-01-01 15:40:00.000','1900-01-01 16:00:00.000','1900-01-01 15:40:00.000','1900-01-01 16:00:00.000','MD Return 20')
,('76020','2124','499',NULL,'1900-01-01 16:00:00.000','1900-01-01 16:20:00.000',NULL,NULL,NULL)
,('76021','2124','499',NULL,'1900-01-01 16:20:00.000','1900-01-01 16:40:00.000',NULL,NULL,NULL)
,('76022','2124','499','191','1900-01-01 16:40:00.000','1900-01-01 17:00:00.000','1900-01-01 16:40:00.000','1900-01-01 17:00:00.000','MD Return 20')
SELECT * FROM #MyData

Upvotes: 0

Views: 2268

Answers (1)

podiluska
podiluska

Reputation: 51494

Firstly, you should study the idea of normalising your data, and storing the different entities (slots, appointments, etc) in different tables.

So what you need to do is find a list of slots.

 SELECT ScheduleTimesId,ScheduleId,FacilityId, ApptTypeId, SlotTimeStart, SlotTimeStop, ApptType 
 FROM #MyData

and a list of appointments that have been made

SELECT ApptTimeStart, ApptTimeStop 
FROM #MyData 
WHERE ApptTimeStart is not null

From there, you need to find which slots don't have any appointments in them; so you do a left join from slots to appointments to see where the slot time intersects with the appointment time, and there is no appointment there.

;with slots as 
    (SELECT ScheduleTimesId,ScheduleId,FacilityId, ApptTypeId, SlotTimeStart, SlotTimeStop, ApptType FROM #MyData ),
appts as 
     (SELECT ApptTimeStart, ApptTimeStop FROM #MyData where ApptTimeStart is not null)
select * from slots
left join appts 
         on slots.SlotTimeStart>=appts.ApptTimeStart 
         and slots.SlotTimeStop<=appts.ApptTimeStop
where appts.ApptTimeStart is null

Upvotes: 1

Related Questions