CODEWITHSUNDEEP
sqlsql-server
John Petrak
John Petrak

Reputation: 2928

How do I identify the last shift given a start shift and duration in SQL Server

I have this example data set

ShiftDate       Description     StartTime                   EndTime                     IsWorkShift
2014-01-01      Day Shift       2013-12-31 21:00:00.000     2014-01-01 09:00:00.000     1
2014-01-01      Night Shift     2014-01-01 09:00:00.000     2014-01-01 21:00:00.000     0
2014-01-02      Day Shift       2014-01-01 21:00:00.000     2014-01-02 09:00:00.000     1
2014-01-02      Night Shift     2014-01-02 09:00:00.000     2014-01-02 21:00:00.000     0
2014-01-03      Day Shift       2014-01-02 21:00:00.000     2014-01-03 09:00:00.000     1
2014-01-03      Night Shift     2014-01-03 09:00:00.000     2014-01-03 21:00:00.000     0
2014-01-04      Day Shift       2014-01-03 21:00:00.000     2014-01-04 09:00:00.000     1
2014-01-04      Night Shift     2014-01-04 09:00:00.000     2014-01-04 21:00:00.000     0
2014-01-05      Day Shift       2014-01-04 21:00:00.000     2014-01-05 09:00:00.000     1
2014-01-05      Night Shift     2014-01-05 09:00:00.000     2014-01-05 21:00:00.000     0

I need to work out the EndTime given a StartTime and a Duration

For example If I supply a StartTime of "2014-01-01 21:00:00.000" and Duration of 24 hours

I would need "2014-01-03 09:00:00.000" returned as the EndTime

2014-01-02  Day Shift       12 Working Hours
2014-01-02  Night Shift      0 Working Hours
2014-01-03  Day Shift       12 Working Hours

I have got this working with a cursor but when I need to use it for say 50 calculations, it gets quite slow.

ALTER FUNCTION [Maintenance].[CalendarDuration]
(
       @ServerID INT,
       @UTCStartTime DATETIME,
       @WorkingDuration INT
)
RETURNS int
AS
BEGIN
       DECLARE @UTCEndTime as Datetime;
       DECLARE @ShiftStartTime as datetime, @ShiftEndTime as datetime;
       DECLARE @ShiftDuration as int;

       DECLARE ShiftCursor CURSOR FAST_FORWARD
              FOR  SELECT t1.StartTime, t1.EndTime
              FROM config.WorkingDayShiftPatterns t1
              WHERE 
              t1.ServerID = @ServerID
              AND t1.EndTime > @UTCStartTime
              AND t1.IsWorkShift = 1
              ORDER BY t1.StartTime;

       OPEN ShiftCursor;

       FETCH NEXT FROM ShiftCursor
       INTO @ShiftStartTime, @ShiftEndTime;

       WHILE @@FETCH_STATUS = 0
       BEGIN 
              IF @ShiftStartTime < @UTCStartTime 
                     SET @ShiftStartTime = @UTCStartTime;

              SET @ShiftDuration = DATEDIFF(minute, @ShiftStartTime, @ShiftEndTime);     
              IF @ShiftDuration >= @WorkingDuration
              BEGIN
                     SET @UTCEndTime = DATEADD(minute, @WorkingDuration, @ShiftStartTime);
                     BREAK;
              END
              ELSE
                     SET @WorkingDuration = @WorkingDuration - @ShiftDuration;

              FETCH NEXT FROM ShiftCursor
              INTO @ShiftStartTime, @ShiftEndTime;
       END 

       CLOSE ShiftCursor;
       DEALLOCATE ShiftCursor;

       RETURN DATEDIFF(minute, @UTCStartTime, @UTCEndTime);
END

Upvotes: 0

Views: 484

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181007

A common table expression can find the number of hours left at each shift's end, so that you easily can pick out the shift where the last hour is worked;

DECLARE @UTCStartTime DATETIME = '2014-01-01 21:00:00.000';
DECLARE @WorkingDuration INT = 24;

WITH WorkingShifts AS (
 SELECT CASE WHEN @UTCStartTime<StartTime 
             THEN StartTime ELSE @UTCStartTime END StartTime, EndTime,
    @WorkingDuration - 
      SUM(DATEDIFF(HH, CASE WHEN @UTCStartTime<StartTime 
                            THEN StartTime ELSE @UTCStartTime 
                            END, EndTime)) 
      OVER (ORDER BY StartTime) left_after_shift
 FROM WorkingDayShiftPatterns 
 WHERE IsWorkShift=1 AND EndTime >= @UTCStartTime
)
SELECT TOP 1 DATEADD(HH, left_after_shift, EndTime) done_at
FROM WorkingShifts
WHERE left_after_shift <= 0
ORDER BY StartTime;

SQLfiddle is a bit tired, so this is tested on SQL Server 2014. Don't have any older version to test on but it should work at least on 2012.

Upvotes: 2

Contisma
Contisma

Reputation: 71

would this work:

select min(EndTime)
from shifts
where EndTime >= DateAdd(hour, @Duration, @StartTime)

Upvotes: 0

Related Questions