跨行的 SQl 服务器日期差异

Question

我已经彻底搜索了，但无法找到其他帖子来展示如何实现这一目标。我正在尝试找到一种简单的方法来计算两个日期之间的总小时数。我的表如下所示：

emp_num  | time                     | punch_type
399      | 2017-07-05 04:44:00.000  | 1
399      | 2017-07-05 14:30:00.000  | 2
399      | 2017-07-06 04:40:00.000  | 1
399      | 2017-07-06 13:31:00.000  | 2

punch_type表示打卡 = 1 和打卡 =2。我想计算同一天两拳之间的时差。然后将这些总数相加，以获得 7/5 和 7/6 的总小时数。

编辑：我忘了提到有时每天可能会有多个打卡/打卡。

关于如何完成的任何想法？

Answer 1

这是一种方法可以做到这一点

查找每天的工作时间

select emp_num,cast(time as date),datediff(hh,min(time),max(time))
from yourtable 
group by emp_num,cast(time as date)

获取 x 到 y 天之间的总小时数

select emp_num,sum(tot_hours)
from 
(
select emp_num,cast(time as date),datediff(hh,min(time),max(time)) as tot_hours
from yourtable 
Where time >= '2017-07-05' and time < dateadd(dd,1,'2017-07-06')
group by emp_num,cast(time as date)
) a
Group by emp_num

注意：这考虑到每天只有两次冲孔，就像您的示例数据一样

Answer 2

您可以对表格进行透视，为您提供正确的布局

数据透视表文档：https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

您可能希望对表进行透视以包含以下列：

• Emp_num
• clock_in
• 下班打卡
差异
• (现在计算为新的上班打卡和下班打卡列之间的日期差异(

您可以按员工和日期对最后一列求和，以获得问题的答案。

Answer 3

CREATE TABLE times
(
emp_num    INT
, [time]     DATETIME
, punch_type TINYINT
);
INSERT INTO times
VALUES (399, '2017-07-05 04:44:00.000', 1)
, (399, '2017-07-05 14:30:00.000', 2)
, (399, '2017-07-06 04:40:00.000', 1)
, (399, '2017-07-06 13:31:00.000', 2);
SELECT t1.emp_num
, CAST(t1.time AS DATE) d
, SUM(DATEDIFF(HOUR, t1.time, t2.time)) total_time
FROM times t1
OUTER APPLY
(   SELECT TOP 1 t2.[time]
FROM times t2
WHERE t2.emp_num = t1.emp_num AND t2.punch_type = 2 AND t2.time > t1.time
ORDER BY t2.time
)       t2
WHERE punch_type = 1
GROUP BY t1.emp_num, CAST(t1.time AS DATE);

Answer 4

这应该会给你你想要的结果......

IF OBJECT_ID('tempdb..#TestData', 'U') IS NULL 
BEGIN   -- DROP TABLE #TestData;
CREATE TABLE #TestData (
emp_num INT NOT NULL,
[time] DATETIME NOT NULL,
punch_type TINYINT NOT NULL 
);
INSERT #TestData (emp_num, [time], punch_type) VALUES
(399, '2017-07-05 04:44:00.000', 1),
(399, '2017-07-05 14:30:00.000', 2),
(399, '2017-07-06 04:40:00.000', 1),
(399, '2017-07-06 13:31:00.000', 2);
END;
--==============================================================
WITH
cte_AddRN AS (
SELECT 
td.emp_num, td.time, td.punch_type, 
RN = ROW_NUMBER() OVER (PARTITION BY td.emp_num, td.punch_type ORDER BY td.time)
FROM
#TestData td
),
cte_PivotInOut AS (
SELECT 
arn.emp_num,
PunchIn = MAX(CASE WHEN arn.punch_type = 1 THEN arn.time END),
PunchOut = MAX(CASE WHEN arn.punch_type = 2 THEN arn.time END)
FROM
cte_AddRN arn
GROUP BY
arn.emp_num,
arn.RN
)
SELECT 
pio.emp_num, 
TotalHours = ROUND(SUM(DATEDIFF(ss, pio.PunchIn, pio.PunchOut) / 3600.0), 2)
FROM
cte_PivotInOut pio
GROUP BY
pio.emp_num;

Answer 5

在这里，试试这个

SELECT a.[emp_num]                                    AS 'Employee Number',
CAST(a.[Time] AS DATE)                  AS 'Time',                          
DATEDIFF(hh , MIN(time), MAX(time))     AS 'Hours'
FROM dbo.times                                                                               AS a
GROUP BY a.[emp_num], CAST(a.[Time] AS DATE)