July 8, 2014 at 2:22 pm
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.
July 8, 2014 at 2:39 pm
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
July 8, 2014 at 5:11 pm
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
July 10, 2014 at 4:36 pm
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply