How to assign shift based on punch time

Question

Based on punch time shift automatically assigned to employee

Table Trnevents:

emp_reader_id   EVENTID             DT
    3               1       2019-07-14 17:00:00.000
    3               0       2019-07-14 10:00:00.000
    3               1       2019-07-13 17:50:00.000
    3               0       2019-07-13 10:05:00.000
    3               1       2019-07-12 16:00:00.000
    3               0       2019-07-12 08:55:00.000


declare 
    @start_date date='2019-07-12'
    ,@end_date date ='2019-07-14'

;WITH ByDays AS 
( -- Number the entry register in each day
    SELECT 
        emp_reader_id,    
        dt AS T,    
        CONVERT(VARCHAR(10),dt,102) AS Day,    
        FLOOR(CONVERT(FLOAT,dt)) DayNumber,    
        ROW_NUMBER() OVER(PARTITION BY FLOOR(CONVERT(FLOAT,dt)) ORDER BY dt) InDay 
    FROM trnevents  
    where   
    (
        CONVERT(VARCHAR(26), dt, 23) >= CONVERT(VARCHAR(26), @start_date, 23) 
        and CONVERT(VARCHAR(26), dt, 23) <=CONVERT(VARCHAR(26), @end_date, 23)
    )
)
,Diffs AS 
(
    SELECT     
        E.Day,
        E.emp_Reader_id,
        E.T ET,
        O.T OT,
        O.T-E.T Diff,  
        DATEDIFF(S,E.T,O.T) DiffSeconds -- difference in seconds
    FROM     
    (
        SELECT 
            BE.emp_Reader_id, 
            BE.T, 
            BE.Day, 
            BE.InDay 
        FROM ByDays BE      
        WHERE BE.InDay % 2 = 1
    ) E -- Even rows
    INNER JOIN    
    (
        SELECT 
            BO.emp_reader_id, 
            BO.T, 
            BO.Day, 
            BO.InDay 
        FROM ByDays BO      
        WHERE BO.InDay % 2 = 0
    ) O -- Odd rows
        ON E.InDay + 1 = O.InDay -- Join rows (1,2), (3,4) and so on
        AND E.Day = O.Day --  in the same day
)
SELECT * FROM Diffs

DECLARE   @start TIME(0) = '9:00 AM',   @end   TIME(0) =  '18:00 PM';
WITH x(n) AS 
(  
    SELECT TOP (DATEDIFF(HOUR, @start, @end) + 1) 
        rn = ROW_NUMBER() OVER (ORDER BY [object_id])   
    FROM sys.all_columns 
    ORDER BY [object_id]
)
SELECT 
    t = DATEADD(HOUR, n-1, @start)
    ,cast(DATEADD(HOUR, n-1, @start) as varchar(50))+' shift' 
FROM x 
ORDER BY t;

If employee punch in time between 8.30 to 9.30 am , it assigned to 9.00 shift if 9.30 to 10.30. it assigned to 10.00 shift

Expected output:

    Day     emp_Reader_id               ET                              OT                  Diff                DiffSeconds         Shift
2019.07.12      3           2019-07-12 08:55:00.000     2019-07-12 16:00:00.000     1900-01-01 07:05:00.000         25500       09:00:00 shift
2019.07.13      3           2019-07-13 10:05:00.000     2019-07-13 17:50:00.000     1900-01-01 07:45:00.000         27900       10:00:00 shift
2019.07.14      3           2019-07-14 12:00:00.000     2019-07-14 21:00:00.000     1900-01-01 07:00:00.000         25200       12:00:00 shift

Answer 1

Two solutions, one with LEAD. First is without LEAD:

select 
    CAST(t1.DT as date)    AS "Day",
    t1.emp_reader_id       AS emp_Reader_id,
    t1.DT                  AS ET,
    t2.DT                  AS OT,
    t1.DT - t2.DT          As Diff,
    DATEDIFF(s, t1.DT, t2.DT) As DiffSeconds,
    cast(dateadd(HOUR,datepart(HH,t1.DT)+ round(datepart(MINUTE,t1.dt)/60.0,0),0) as time) as Shift
from trnevents   t1
inner join trnevents   t2 on t2.emp_reader_id=t1.emp_reader_id and t2.EVENTID=1 and CAST(t2.DT as date)= CAST(t1.DT as date)
where t1.eventID=0
order by t1.DT

or:

SELECT 
    Day, 
    emp_reader_id, 
    ET, 
    OT, 
    ET-OT AS Diff ,
    DATEDIFF(s,ET,OT) as DiffSeconds,
    cast(dateadd(HOUR,datepart(HH,ET)+ round(datepart(MINUTE,ET)/60.0,0),0) as time) as Shift
FROM (
select 
    CAST(t1.DT as date)    AS "Day",
    t1.emp_reader_id       AS emp_Reader_id,
    t1.DT                  AS ET,
    LEAD(t1.DT) over (order by emp_reader_id,dt) AS OT,
    eventid,
    --t1.DT - t2.DT          As Diff,
    --DATEDIFF(s, t1.DT, t2.DT) As DiffSeconds,
    cast(dateadd(HOUR,datepart(HH,t1.DT)+ round(datepart(MINUTE,t1.dt)/60.0,0),0) as time) as Shift
from trnevents   t1) x
where x.EVENTID=0

Both query produce same result (second one is probably quicker)

Answer 2

If employee punch in time between 8.30 to 9.30 am , it assigned to 9.00 shift if 9.30 to 10.30. it assigned to 10.00 shift

If I understand this correctly, you can use a case expression:

select e.*,
       (case when dt >= '08:30:00' and dt < '09:30:00'
             then 'Shift 09:00'
             when dt >= '09:30:00' and dt < '10:30:00'
             then 'Shift 10:00'
        end) as shift
from Trnevents e

If you want a more general solution where the breaks are at 30 minute intervals throughout the day, then subtract 30 minutes and extract the hour:

select e.*,
       datepart(hour, dateadd(minute, -30, dt)) as shift
from e;