Calculating working hours excluding total break time

Question

I'm trying to calculate the working hours of employees while excluding the Break-in and Break-out base on their Time Reference. For now I'm using this function to accomplish my partial goal.

EDIT

Sample Problem:

Employee 1 has

Time Start: Dec 18 2017 8:00AM - Time End: Dec 18 2017 17:00PM

Break-in: 12:00 PM - Break-out: 12:30 PM

Please take note that the Time Start and End is user input while the Break-in and out is from Database

SELECT DATEDIFF(HOUR, 'Dec 18 2017 8:00', 'Dec 18 2017 17:00')

Current Output: 9hrs

Expected Output: 8.5hrs because it is subtracted from the total break time

What is the actual question? I would bet that you might not even need to use a function here, and I would personally much rather inherit a query from you anyway. Maybe show us some sample data and the expected output. — Tim Biegeleisen, Dec 19 '17 at 07:00
sum up the break times using datediff on the day used to get the total hours and substract them from the total hours? — Patrick Artner, Dec 19 '17 at 07:13
@PatrickArtner That was my first idea. But how about if the start or end time falls in between the break time? — Yuu, Dec 19 '17 at 07:16
your data is flawed - teach your employees to stamp in/out correctly ;) - ignore break_ins before 8:00 - ignore break-outs after 17:00 and replace them with 8:00 or 17:00 before calculating the break & subtracting. Provide some demodata with edgecases that you need to have considered. — Patrick Artner, Dec 19 '17 at 07:19

score 1 · Accepted Answer · answered Dec 19 '17 at 09:06

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

Calculating working hours excluding total break time

1 Answers1