locked
find shift time between two time RRS feed

  • Question

  • hi

     i am using sql server 2008

    i have ShiftMaster table where each machine is allocate start time and  end time, machine can be multiple shift. there some jobe is process on machine.

    i.e ShiftMaster  e.g as 

    MachineID	SHIFTID		STIME			ETIME
    A110T02		1		07:00:00.0000000	16:00:00.0000000
    A110T02		2		14:00:00.0000000	23:00:00.0000000
    A110T02		3		23:00:00.0000000	07:00:00.0000000

    if i pass machine Name , start Job date time  and End Job time  it should retune shift start time  and End time.

    i am facing problem with third shift because it involve mid night time i.e two days

    i am expecting  if provide input like this

    @machineName = 'A110T02'

    @startJobdatetime = '2015-12-01 23:02:00'

    @EndJobdatetime = '2015-12-01 23:20:00'

    result should  return

    3		23:00:00.0000000	07:00:00.0000000

    note : if  @startJobdatetime and  @EndJobdatetime  overlap between two shift then highest  


    Thanks Best Regard Naweez




    • Edited by naweez Monday, December 14, 2015 12:34 PM add note
    Monday, December 14, 2015 7:45 AM

Answers

  • Hi naweez,

    Based on my understanding, to get the expected output, you can reference below sample.

    DECLARE @Tmp table (MachineID VARCHAR(20),SHIFTID INT,STIME VARCHAR(20),ETIME VARCHAR(20))	
    INSERT @Tmp SELECT 'A110T02',1,'07:00:00.0000000','16:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',2,'14:00:00.0000000','23:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',3,'23:00:00.0000000','07:00:00.0000000'
     
    DECLARE @machineName VARCHAR(20) = 'A110T02'
    -- this @startJobDateTime and @EndJobdatetime covers shift 1 and 3
    DECLARE @startJobdatetime DATETIME= '2015-12-01 23:02:00'
    DECLARE @EndJobdatetime DATETIME= '2015-12-02 13:52:00'
    SELECT @startJobdatetime STIME, DATEADD(HOUR,DATEDIFF(HOUR,0,@startJobdatetime)+1,0) ETIME
    
    ;WITH Cte AS
    (
    SELECT MachineID,shiftID,
    CAST(('19000101 '+STIME) AS DATETIME2) STIME, 
    CASE WHEN ETIME<STIME THEN CAST(('19000102 '+ETIME) AS DATETIME2) 
    ELSE  CAST(('19000101 '+ETIME) AS DATETIME2)
    END ETIME,ETIME ETIME2,STIME STIME2
    FROM @Tmp
    ),
    Cte2 AS --calculate the hours that covered by each shift
    (
    SELECT MachineID,shiftID,STIME, DATEADD(HOUR,1,STIME) ETIME, ETIME LastHour,STIME2,ETIME2 FROM Cte
    UNION ALL
    SELECT MachineID,shiftID,DATEADD(HOUR,1,STIME),DATEADD(HOUR,2,STIME),LastHour, STIME2,ETIME2 FROM Cte2
    WHERE DATEADD(HOUR,2,STIME)<=LastHour
    ),
    Cte3 AS --calculate the hours that covered by the @startJobDatetime and @endJobDatetime
    (
    SELECT @startJobdatetime STIME, DATEADD(HOUR,DATEDIFF(HOUR,0,@startJobdatetime)+1,0) ETIME
    UNION ALL
    SELECT ETIME, CASE WHEN DATEADD(HOUR,1,ETIME)>@EndJobdatetime THEN @EndJobdatetime ELSE DATEADD(HOUR,1,ETIME) END  FROM Cte3
    WHERE ETIME<@EndJobdatetime
    )
    SELECT DISTINCT MachineID,SHIFTID,STIME2 STIME,ETIME2 ETIME  FROM CTE2 c
    WHERE EXISTS
    (--calculate the the overlap of those hours
    SELECT 1 FROM Cte3 
    WHERE CAST(STIME AS TIME) BETWEEN CAST(C.STIME AS TIME) AND CASE WHEN CAST(C.ETIME AS TIME)='00:00:00' THEN '23:59:59' ELSE CAST(ETIME AS TIME) END
    
    ) 

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    • Proposed as answer by HoroChan Tuesday, December 15, 2015 5:56 AM
    • Marked as answer by Eric__Zhang Friday, December 25, 2015 5:58 AM
    Monday, December 14, 2015 11:28 AM

All replies

  • Hi,

    You need to add shiftid as well in your query filter

    DECLARE @Tmp table (MachineID VARCHAR(20),SHIFTID INT,STIME VARCHAR(25),ETIME VARCHAR(25))	
    INSERT @Tmp SELECT 'A110T02',1,'07:00:00.0000000','16:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',2,'14:00:00.0000000','23:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',3,'23:00:00.0000000','07:00:00.0000000'
    
    
    DECLARE @machineName VARCHAR(20) = 'A110T02'
     ,@startJobdatetime VARCHAR(25) = '2015-12-01 23:02:00' 
     ,@EndJobdatetime VARCHAR(25) = '2015-12-01 23:20:00'
     ,@ShiftId INT = 3
    
    
    SELECT * FROM @Tmp
    WHERE MachineID = @machineName
    AND SHIFTID = @ShiftId
    AND (STIME > CAST(@startJobdatetime AS TIME) OR ETIME < CAST(@EndJobdatetime AS TIME))

    Refer Adventureworks sample database for shift table design:

    USE AdventureWorks
    SELECT * FROM HumanResources.Shift


    sathya -
    My blog
    ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, December 14, 2015 8:52 AM
  • thanks for reply.

    i don't have shift id, without shift it giving result.

    DECLARE @Tmp table (MachineID VARCHAR(20),SHIFTID INT,STIME VARCHAR(25),ETIME VARCHAR(25))
    INSERT @Tmp SELECT 'A110T02',1,'07:00:00.0000000','16:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',2,'14:00:00.0000000','23:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',3,'23:00:00.0000000','07:00:00.0000000'
    
    
    DECLARE @machineName VARCHAR(20) = 'A110T02'
     ,@startJobdatetime VARCHAR(25) = '2015-12-01 23:02:00' 
     ,@EndJobdatetime VARCHAR(25) = '2015-12-01 23:30:00'
     --,@ShiftId INT = 3
    
    
    SELECT * FROM @Tmp
    WHERE MachineID = @machineName
    --AND SHIFTID = @ShiftId
    AND (STIME > CAST(@startJobdatetime AS TIME) OR ETIME < CAST(@EndJobdatetime AS TIME))
    
    
    result
    
    MachineID	SHIFTID		STIME			ETIME
    A110T02		1		07:00:00.0000000	16:00:00.0000000
    A110T02		2		14:00:00.0000000	23:00:00.0000000
    A110T02		3		23:00:00.0000000	07:00:00.0000000
    


    Thanks Best Regard Naweez


    • Edited by naweez Monday, December 14, 2015 9:02 AM formated
    Monday, December 14, 2015 9:00 AM
  • hi

     i am using sql server 2008

    i have ShiftMaster table where each machine is allocate start time and  end time, machine can be multiple shift. there some jobe is process on machine.

    i.e ShiftMaster  e.g as 

    MachineID	SHIFTID		STIME			ETIME
    A110T02		1		07:00:00.0000000	16:00:00.0000000
    A110T02		2		14:00:00.0000000	23:00:00.0000000
    A110T02		3		23:00:00.0000000	07:00:00.0000000

    if i pass machine Name , start Job date time  and End Job time  it should retune shift start time  and End time.

    i am facing problem with third shift because it involve mid night time i.e two days

    i am expecting  if provide input like this

    @machineName = 'A110T02'

    @startJobdatetime = '2015-12-01 23:02:00'

    @EndJobdatetime = '2015-12-01 23:20:00'

    result should  return

    3		23:00:00.0000000	07:00:00.0000000



    Thanks Best Regard Naweez



    i try it working  for 1 & 2 shift but not for 3rd shift


    declare @stratTime as datetime = '2015-12-01 14:02:00'
    declare @EndTime as  datetime = '2015-12-01 22:09:00'
    declare @MachinID as  nvarchar(50) = 'A110T02'
    
    SELECT top(1) [RESID]
          ,[SHIFTID]
          ,cast(@stratTime as date) + cast(STIME as datetime)
          ,cast(@stratTime as date) + cast(ETIME as datetime) 
      FROM [SWISSPRD_App].[dbo].[View_ResourceShiftMaster2]
     WHERE MachinID= @MachinID							--23.00 pm      am 7.00      
    and MachinID is not null
      AND  @stratTime  BETWEEN   cast(@stratTime as date) + cast(STIME as datetime) 
    				   AND       cast(@stratTime as date) + cast(ETIME as datetime) 
    and
    
      @EndTime  BETWEEN   cast(@EndTime as date) + cast(STIME as datetime) 
      AND   cast(@EndTime as date) + cast(ETIME as datetime)
    order by  [SHIFTID] desc



    Thanks Best Regard Naweez


    • Edited by naweez Monday, December 14, 2015 10:23 AM formated
    Monday, December 14, 2015 10:22 AM
  • Hi naweez,

    Based on my understanding, to get the expected output, you can reference below sample.

    DECLARE @Tmp table (MachineID VARCHAR(20),SHIFTID INT,STIME VARCHAR(20),ETIME VARCHAR(20))	
    INSERT @Tmp SELECT 'A110T02',1,'07:00:00.0000000','16:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',2,'14:00:00.0000000','23:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',3,'23:00:00.0000000','07:00:00.0000000'
     
    DECLARE @machineName VARCHAR(20) = 'A110T02'
    -- this @startJobDateTime and @EndJobdatetime covers shift 1 and 3
    DECLARE @startJobdatetime DATETIME= '2015-12-01 23:02:00'
    DECLARE @EndJobdatetime DATETIME= '2015-12-02 13:52:00'
    SELECT @startJobdatetime STIME, DATEADD(HOUR,DATEDIFF(HOUR,0,@startJobdatetime)+1,0) ETIME
    
    ;WITH Cte AS
    (
    SELECT MachineID,shiftID,
    CAST(('19000101 '+STIME) AS DATETIME2) STIME, 
    CASE WHEN ETIME<STIME THEN CAST(('19000102 '+ETIME) AS DATETIME2) 
    ELSE  CAST(('19000101 '+ETIME) AS DATETIME2)
    END ETIME,ETIME ETIME2,STIME STIME2
    FROM @Tmp
    ),
    Cte2 AS --calculate the hours that covered by each shift
    (
    SELECT MachineID,shiftID,STIME, DATEADD(HOUR,1,STIME) ETIME, ETIME LastHour,STIME2,ETIME2 FROM Cte
    UNION ALL
    SELECT MachineID,shiftID,DATEADD(HOUR,1,STIME),DATEADD(HOUR,2,STIME),LastHour, STIME2,ETIME2 FROM Cte2
    WHERE DATEADD(HOUR,2,STIME)<=LastHour
    ),
    Cte3 AS --calculate the hours that covered by the @startJobDatetime and @endJobDatetime
    (
    SELECT @startJobdatetime STIME, DATEADD(HOUR,DATEDIFF(HOUR,0,@startJobdatetime)+1,0) ETIME
    UNION ALL
    SELECT ETIME, CASE WHEN DATEADD(HOUR,1,ETIME)>@EndJobdatetime THEN @EndJobdatetime ELSE DATEADD(HOUR,1,ETIME) END  FROM Cte3
    WHERE ETIME<@EndJobdatetime
    )
    SELECT DISTINCT MachineID,SHIFTID,STIME2 STIME,ETIME2 ETIME  FROM CTE2 c
    WHERE EXISTS
    (--calculate the the overlap of those hours
    SELECT 1 FROM Cte3 
    WHERE CAST(STIME AS TIME) BETWEEN CAST(C.STIME AS TIME) AND CASE WHEN CAST(C.ETIME AS TIME)='00:00:00' THEN '23:59:59' ELSE CAST(ETIME AS TIME) END
    
    ) 

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    • Proposed as answer by HoroChan Tuesday, December 15, 2015 5:56 AM
    • Marked as answer by Eric__Zhang Friday, December 25, 2015 5:58 AM
    Monday, December 14, 2015 11:28 AM
  • i try from some where site some for input it giving good result but when i put

    DECLARE @machineName VARCHAR(20) = 'A110T02'
     ,@startJobdatetime VARCHAR(25) = '2015-12-01 00:06:00' 
     ,@EndJobdatetime VARCHAR(25) = '2015-12-01 06:33:00'
    it giving null return

    DECLARE @Tmp table (MachineID VARCHAR(20),SHIFTID INT,STIME VARCHAR(25),ETIME VARCHAR(25)) 
    INSERT @Tmp SELECT 'A110T02',1,'07:00:00.0000000','16:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',2,'14:00:00.0000000','23:00:00.0000000'
    INSERT @Tmp SELECT 'A110T02',3,'23:00:00.0000000','07:00:00.0000000'
    
    DECLARE @machineName VARCHAR(20) = 'A110T02'
     ,@startJobdatetime VARCHAR(25) = '2015-12-01 23:02:00' 
     ,@EndJobdatetime VARCHAR(25) = '2015-12-01 23:20:00'
    
    SELECT *  FROM @Tmp WHERE 
        MachineID = @machineName 
        AND 
    cast(cast(CAST(@startJobdatetime AS date) as varchar(10))+' '+left(stime,8) as datetime)
           <= cast(@startJobdatetime as datetime)
        and
    cast(cast(CAST(case when cast(ETIME as time)<= cast(STIME as time) 
    then  dateadd(day,1,@startJobdatetime) 
    else @startJobdatetime end AS date) as varchar(10))+' '+left(ETIME,8) as datetime)
     >= cast(@EndJobdatetime as datetime)


    Thanks Best Regard Naweez


    • Marked as answer by naweez Tuesday, December 15, 2015 3:40 AM
    • Unmarked as answer by naweez Tuesday, December 15, 2015 5:48 AM
    • Edited by naweez Tuesday, December 15, 2015 5:50 AM not ans.
    Tuesday, December 15, 2015 3:40 AM
  • Hi naweez,

    Have you checked my last reply? It would identify the shifts(1 or more ) that covered by @startJobDateTime and @EndJobdatetime.

    Eric Zhang
    TechNet Community Support

    Tuesday, December 15, 2015 7:47 AM