SQL Server : workers shift excluding break times

Question

I just write this function in SQL Server. It calculate working minutes of worker excluding break times.

ALTER FUNCTION getMinutesWork
    ( @startT dateTime,
      @endT dateTime)
AS
BEGIN
    DECLARE @result int = DATEDIFF(mi, @startT, @endT)  --time difference including break times
    SET @startT = CAST(@startT AS TIME)
    SET @endT = CAST(@endT AS TIME)

    -- work schedule  
    DECLARE @t1 datetime = CAST('08:00:00' AS TIME),  --work
            @t2 datetime = CAST('10:00:00' AS TIME),  --break                   
            @t3 datetime = CAST('10:15:00' AS TIME),  --w
            @t4 datetime = CAST('12:40:00' AS TIME),  --b
            @t5 datetime = CAST('13:25:00' AS TIME),  --w
            @t6 datetime = CAST('16:30:00' AS TIME),  --b 
            @t7 datetime = CAST('16:45:00' AS TIME),  --w
            @t8 datetime = CAST('18:15:00' AS TIME);  --b

    --excluding break times
    IF ((@startT <= @t2 AND @endT >@t2) AND (@startT < @t3 AND @endT >= @t3))
       SET @result = @result - DATEDIFF(mi, @t2, @t3) 

    IF ((@startT <= @t4 AND @endT >@t4) AND (@startT < @t5 AND @endT >= @t5))
       SET @result = @result - DATEDIFF(mi, @t4, @t5)

    IF ((@startT <= @t6 AND @endT >@t6) AND (@startT < @t7 AND @endT >= @t7))
       SET @result = @result - DATEDIFF(mi, @t6, @t7)

    IF ((@startT > @t8) AND (@endT > @t1))
       SET @result = @result - DATEDIFF(mi, @t1, @startT)

    --excluding break times if worker starts on break time
    SET @result = (CASE 
                     WHEN (@startT < @t1) 
                       THEN @result - DATEDIFF(mi, @startT, @t1)
                     WHEN (@startT > @t2 AND @startT < @t3) 
                       THEN @result - DATEDIFF(mi, @startT, @t3)
                     WHEN (@startT > @t4 AND @startT < @t5) 
                       THEN @result - DATEDIFF(mi, @startT, @t5)
                     WHEN (@startT > @t6 AND @startT < @t7) 
                       THEN @result - DATEDIFF(mi, @startT, @t7)
                     WHEN (@startT > @t8) AND (@endT < @t1)
                       THEN 0
                     ELSE @result
                 END)

----------'2004-10-19 20:00:00','2004-10-18 10:00:00'------------
    --excluding break times if worker finish his work on break time
    SET @result = (
CASE 
    WHEN (@endT < @t1) 
        THEN 0
    WHEN (@endT >= @t2 AND @endT < @t3) 
        THEN @result - DATEDIFF(mi,@t2,@endT)
    WHEN (@endT >= @t4 AND @endT < @t5) 
        THEN @result - DATEDIFF(mi,@t4,@endT)
    WHEN (@endT >= @t6 AND @endT < @t7) 
        THEN @result - DATEDIFF(mi,@t6,@endT)
    WHEN (@endT > @t8)
        THEN @result - DATEDIFF(mi,@t8,@endT)
    ELSE @result
END )

-----------------------------------------------------------------------------------

IF @result <0
    SET @result = 0
-----------------------------------------------------------------------------------

RETURN @result
End

It works fine when worker comes and goes in same day. But when worker come like 11pm and works until tomorrow 11am it doesn't exclude break times. Any idea please? Sorry for long post btw.

Edit: I found the problem but can't solve it. If there is a overday work like @startT = '20:00:00' and @endT = '10:20:00', this part won't work:

IF ((@startT <= @t2 AND @endT >@t2) AND (@startT < @t3 AND @endT >= @t3))
    SET @result = @result - DATEDIFF(mi,@t2,@t3) 
IF ((@startT <= @t4 AND @endT >@t4) AND (@startT < @t5 AND @endT >= @t5))
    SET @result = @result - DATEDIFF(mi,@t4,@t5)
IF ((@startT <= @t6 AND @endT >@t6) AND (@startT < @t7 AND @endT >= @t7))
    SET @result = @result - DATEDIFF(mi,@t6,@t7)

How can I solve that part?

Deep Kalra · Accepted Answer

Can you please check if this works? I think this resolves the logical issue.

DECLARE @startT DATETIME = Cast('19:00:00' AS TIME), 
        @endT   DATETIME = Cast('11:00:00' AS TIME); 
DECLARE @result INT = Datediff(mi, @startT, @endT); 
DECLARE @t1 DATETIME = Cast('08:00:00' AS TIME),--work 
        @t2 DATETIME = Cast('10:00:00' AS TIME),--break                    
        @t3 DATETIME = Cast('10:15:00' AS TIME),--w 
        @t4 DATETIME = Cast('12:40:00' AS TIME),--b 
        @t5 DATETIME = Cast('13:25:00' AS TIME),--w 
        @t6 DATETIME = Cast('16:30:00' AS TIME),--b  
        @t7 DATETIME = Cast('16:45:00' AS TIME),--w 
        @t8 DATETIME = Cast('18:15:00' AS TIME); --b 

IF Datediff(mi, @startT, Cast('18:00:00' AS TIME)) < 0 
--I have assumed the employee cannot come before six for the night shift
  BEGIN 
      SET @result = @result + 1440; 
      IF (( @endT >= @t3 )) 
        SET @result = @result - Datediff(mi, @t2, @t3) 
      IF (( @endT >= @t5 )) 
        SET @result = @result - Datediff(mi, @t4, @t5) 
      IF (( @endT >= @t7 )) 
        SET @result = @result - Datediff(mi, @t6, @t7) 
  END 
ELSE 
   BEGIN
		IF ((@startT <= @t2 AND @endT >@t2) AND (@startT < @t3 AND @endT >= @t3))
		   SET @result = @result - DATEDIFF(mi, @t2, @t3) 
		IF ((@startT <= @t4 AND @endT >@t4) AND (@startT < @t5 AND @endT >= @t5))
		   SET @result = @result - DATEDIFF(mi, @t4, @t5)
		IF ((@startT <= @t6 AND @endT >@t6) AND (@startT < @t7 AND @endT >= @t7))
		   SET @result = @result - DATEDIFF(mi, @t6, @t7)
	END
SELECT @result 
--SELECT RIGHT('0' + CAST((@result / 60) % 60 AS VARCHAR),2)  + ':' +
--RIGHT('0' + CAST(@result % 60 AS VARCHAR),2)

SQL Server : workers shift excluding break times

Answers (2)

Related Questions