时间戳

首页 > 解决方案 > 如何根据打卡时间分配班次

sql - 如何根据打卡时间分配班次

问题描述

基于自动分配给员工的打卡时间班次

表 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;

如果员工在上午 8.30 到 9.30 之间打卡，如果 9.30 到 10.30，则分配到 9.00 班次。它分配到 10.00 班次

预期输出：

    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

标签： sqlsql-server

解决方案

如果员工在上午 8.30 到 9.30 之间打卡，如果 9.30 到 10.30，则分配到 9.00 班次。它分配到 10.00 班次

如果我理解正确，您可以使用以下case表达式：

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

如果您想要一个更通用的解决方案，其中休息时间为全天 30 分钟间隔，则减去 30 分钟并提取小时：

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

推荐阅读