select 1, cast('2:00' as time), cast('5:00' as time) union all

select 2, cast('3:00' as time), cast('6:00' as time) union all

select 3, cast('2:00' as time), cast('6:00' as time) union all

select 4, cast('6:00' as time), cast('7:00' as time)

)

, slices(Appt_Start, Appt_End) as (

select Appt_Start, Appt_End from (

select dt as Appt_Start, lead(dt) over(order by dt, typ) as Appt_End

from (

select Appt_Start as dt, 1 as typ from Appointments

union all

select Appt_End, -1 from Appointments

) d

) d

where Appt_Start < Appt_End

)

select Appt_ID, classification, min(segment_Start) as segment_Start, max(segment_end) as segment_end

from (

select Appt_ID,

case

when rn_by_slice = 1 then 'NOT OVERBOOKED'

else 'OVERBOOKED' end as classification,

segment_Start, segment_end

from (

select d.Appt_ID, s.Appt_Start as segment_Start, s.Appt_End as segment_End,

d.Appt_Start, d.Appt_End,

row_number() over(partition by s.Appt_Start, s.Appt_End order by d.Appt_ID) as rn_by_slice

from slices s

join Appointments d

on least(d.Appt_End - INTERVAL '1' SECOND, s.Appt_End - INTERVAL '1' SECOND) >= greatest(d.Appt_Start, s.Appt_Start)

) d

) d

group by Appt_ID, classification

order by Appt_ID, segment_Start, segment_end

;