CODEWITHSUNDEEP
sql-serverdatetime
Yuu
Yuu

Reputation: 619

Calculate working hours using DATEDIFF in SQL Server

I'm currently having a hard time calculating the total work hours of the employee, especially on the night shift people. Here is my code:

DECLARE @StartTime time
DECLARE @EndTime time

SELECT @StartTime = CAST('Jun 04 2018 8:00PM' as time)
SELECT @EndTime = CAST('Jun 04 2018 5:00AM' as time)

SELECT
SUM((DATEDIFF(MINUTE, @StartTime, @EndTime) - isnull(datediff(minute, break_start, break_end), 0))/ 60.0) 
FROM pmis.dbo.employee as a
LEFT JOIN pmis.dbo.time_reference as b ON b.code = ISNULL(a.TimeReference, 'TIME14')
    cross apply
    (
        select  break_start = case  when b.break_from between @StartTime and @EndTime
                 then b.break_from
                 when @StartTime between b.break_from and b.break_to
                 then @StartTime
                 else NULL
                end,
                break_end  = case   when b.break_to between @StartTime and @EndTime
                 then b.break_to
                 when @EndTime between b.break_from and b.break_to
                 then @EndTime
                end
        ) as d
WHERE
a.Shift = 0 and a.eid = 7078

What I get is when the given time is like this

@StartTime = 8:00 AM
@EndTime = 5:00 PM

THEN the result is 8.00 hrs exactly as I wanted but if it is like this

@StartTime = 8:00 PM
@EndTime = 5:00 AM

THEN I get -15.00 How to solve this? Thanks

Upvotes: 0

Views: 174

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Another way to solve it would be to use datetime data type instead of time, then you need to change these lines

DECLARE @StartTime datetime
DECLARE @EndTime datetime

SELECT @StartTime = CAST('Jun 04 2018 8:00PM' as datetime)
SELECT @EndTime = CAST('Jun 04 2018 5:00AM' as datetime)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try adding 1 day (1440 minutes) to the calculation when endtime when it is earlier than starttime. This will not handle breaks in graveyard shifts though. But your previous code had the same issue

SELECT
SUM((DATEDIFF(MINUTE, @StartTime, @EndTime)+ 
  CASE WHEN @StartTime > @EndTime THEN 1440 ELSE 0 END -- CHANGE IN CODE IS HERE
  - isnull(datediff(minute, break_start, break_end), 0))/ 60.0) 
FROM pmis.dbo.employee as a
LEFT JOIN pmis.dbo.time_reference as b 
ON b.code = ISNULL(a.TimeReference, 'TIME14')
    cross apply
    (
        select  break_start = case  when b.break_from between @StartTime and @EndTime
                 then b.break_from
                 when @StartTime between b.break_from and b.break_to
                 then @StartTime
                 else NULL
                end,
                break_end  = case   when b.break_to between @StartTime and @EndTime
                 then b.break_to
                 when @EndTime between b.break_from and b.break_to
                 then @EndTime
                end
        ) as d
WHERE
a.Shift = 0 and a.eid = 7078

Upvotes: 1

Related Questions