Calculating working hours excluding total break time

Solution 1:

declare @break_in   time(0) = '12:00',
        @break_out  time(0) = '12:30'

declare @sample table
(
    time_start  time(0),
    time_end    time(0)
)

insert into @sample select '08:00', '17:00'
insert into @sample select '08:00', '11:00'
insert into @sample select '08:00', '12:15'
insert into @sample select '12:15', '17:00'
insert into @sample select '13:00', '17:00'
insert into @sample select '12:10', '12:20'


select  
        s.time_start, s.time_end, 
        total_mins = datediff(minute, time_start, time_end) / 60.0,
        b.break_start, b.break_end,
        total_break = isnull(datediff(minute, break_start, break_end)  / 60.0, 0),
        work_hours = (datediff(minute, time_start, time_end) - isnull(datediff(minute, break_start, break_end), 0))/ 60.0
from    @sample s
        cross apply
        (
            select  break_start = case  when @break_in between time_start and time_end
                        then @break_in
                        when time_start between @break_in and @break_out
                        then time_start
                        else NULL
                        end,
                    break_end  = case   when @break_out between time_start and time_end
                        then @break_out
                        when time_end between @break_in and @break_out
                        then time_end
                        end
    ) b