question

Chinnu avatar image
Chinnu asked

sql function to calculate work hours

I have to calculate the total work hours of employees.For eg:If an employee has a shift from 7pm to 3 pm.If the time in is 7:30pm and time out is 9:30pm.Then work hours is 2 hrs..then time in is 10:00pm and time out is 1:00am..here work hour is 3 hours.but after 12 am,the work hour will be for next day..so..in the second case,only 2hrs is work hour...remaining 1hr for next day..please help me to write the sql function to add the remaining work hours to the next day

sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Squirrel 1 avatar image
Squirrel 1 answered

Any effect of shift time 7 pm to 3 pm on the work hours ?

declare @time_in    datetime,            
    @time_out   datetime            
            
select  @time_in    = '22:00',            
    @time_out   = '01:00'            
            
select  datediff(minute, time_in, time_out)             
from    (            
        select  time_in     = @time_in,            
            time_out    = case  when @time_out < @time_in               
                        then dateadd(day, 1, @time_out)            
                        else @time_out            
                        end            
    ) t            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.