Help with query to find free time in a patient's schedule

Question

Help with query to find free time in a patient's schedule

cmbrown

SSC Journeyman

Points: 84
More actions
July 8, 2014 at 2:22 pm

#147036

Hi,
I have query that returns the scheduled events for a patient. I need to find a way to report the free time in the patient's schedule so we can assign other events or activities.
Here is a sample of the data:
PtIDApptDateActivityFromToTimeLocation
1237/7/2014 0:00Opening Group9:00 - 9:30aXYZ Campus
1237/7/2014 0:00Opening Group9:30 - 10:00aXYZ Campus
1237/7/2014 0:001:1 ST10:30 - 11:00aXYZ Campus
1237/7/2014 0:00Conditioning Group11:00 - 11:30aXYZ Campus
1237/7/2014 0:00Conditioning Group11:30a - 12:00pXYZ Campus
1237/7/2014 0:00Menu Planning Group1:00 - 1:30pXYZ Campus
1237/7/2014 0:00Menu Planning Group1:30 - 2:00pXYZ Campus
1237/7/2014 0:00Life 101 Group3:00 - 3:30pXYZ Campus
1237/7/2014 0:00Life 101 Group3:30 - 4:00pXYZ Campus
1237/7/2014 0:00Medical Appt4:00 - 4:30pXYZ Campus
1237/7/2014 0:00Medical Appt4:30 - 5:00pXYZ Campus
1237/7/2014 0:00Laundry Day5:00 - 5:30pXYZ Campus
The query I need would return
123, 7/7/2014, 10:00 - 10:30
123, 7/7/2014, 12:00 PM - 1:00 PM
I am using SQL Server 2005
the raw data for all dates and start and end times for the appointments are datetime datatypes
Any help would be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

well, it's not going to be easy, but this will get you started;

because you are not using the right data types (a string containing '9:00 - 9:30a' instead of two fields, a start time and end time of datatype time,you have

to chop the data up, and cast it to time.

then you can do gaps and islands logic to find open times.

you said above that your start and ends were dates, but that's not what exists int he sample data.

do exactly like i did, and provide sample data that is copy-and-pasteable to SSMS, and we can actually help with your example.

;WITH MyCTE([PtID],[ApptDate],[Activity],[FromToTime],[Location])

AS

(

SELECT '123','7/7/2014 0:00','Opening Group','9:00 - 9:30a','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Opening Group','9:30 - 10:00a','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','1:1 ST','10:30 - 11:00a','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Conditioning Group','11:00 - 11:30a','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Conditioning Group','11:30a - 12:00p','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Menu Planning Group','1:00 - 1:30p','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Menu Planning Group','1:30 - 2:00p','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Life 101 Group','3:00 - 3:30p','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Life 101 Group','3:30 - 4:00p','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Medical Appt','4:00 - 4:30p','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Medical Appt','4:30 - 5:00p','XYZ Campus' UNION ALL

SELECT '123','7/7/2014 0:00','Laundry Day','5:00 - 5:30p','XYZ Campus'

)

SELECT *,fn1.Item As StartTime,fn2.Item AS EndTime

FROM MyCTE

CROSS APPLY(SELECT Item FROM dbo.DelimitedSplit8K([FromToTime],'-')fn WHERE ITemNumber = 1) fn1

CROSS APPLY(SELECT Item FROM dbo.DelimitedSplit8K([FromToTime],'-')fn WHERE ITemNumber = 2) fn2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cmbrown SSC Journeyman Points: 84 More actions · Answer 2

Thanks much Lowell.

I thought about that on my way home. The TimeFromTo was from a view that cast the times as varchar and concatenated to a single string. Here is the actual data:

PtIDApptDateApptDateTimeFromTimeToActivity

14247/7/2014 0:0012/30/1899 9:00:00 AM12/30/1899 10:00:00 AMOpening Group

14247/7/2014 0:0012/30/1899 10:00:00 AM12/30/1899 11:00:00 AM1:1 ST Jerilyn

14247/7/2014 0:0012/30/1899 11:00:00 AM12/30/1899 12:00:00 PMConditioning Group

14247/7/2014 0:0012/30/1899 1:00:00 PM12/30/1899 2:00:00 PMMenu Planning Group

14247/7/2014 0:0012/30/1899 3:00:00 PM12/30/1899 4:00:00 PMLife 101 Group

14247/7/2014 0:0012/30/1899 4:15:00 PM12/30/1899 4:45:00 PMMedical Appt

14247/7/2014 0:0012/30/1899 5:00:00 PM12/30/1899 5:15:00 PMLaundry Day

Chris

autoexcrement SSCertifiable Points: 5951 More actions · Answer 3

If you can post some DDL to create/insert the tables/data--the underlying data, not the concatenated string version--I'm sure someone will be able to help you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura