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