在 SQL 中使用 2 个不同的列计算 'First-row' 和'Second-row'之间的差异(以分钟为单位)

Question

我有下表，从上午12点开始，我必须计算每个房间和每天的"第一排">Emp_Out和"第二排">Emp_in之间的分钟差。

表格：

Date        EMP_ID        Room       Emp_IN       Emp_OUT        Difference(In Min)
-----       ------        ------     ------       -------        ------------------
9/1/22      001           Room 1     04:30        05:00           270              (First diff is calulated from 12am - Emp_IN)
9/1/22      002           Room 1     05:25        05:42           7
9/1/22      003           Room 1     05:48        06:13           6
9/1/22      001           Room 2     05:00        05:17           300              (First diff is calulated from 12am - Emp_IN)
9/1/22      002           Room 2     05:36        05:48           19
9/1/22      003           Room 2     05:51        06:05           3

LAG可以用于它吗？或者我缺少一个可以帮助的逻辑？

Answer 1

使用LAG获取下一行值，使用Room对行进行分区

房间当天的第一个条目将获得Null，并将其替换为'00:00'，这意味着上午12点。

SELECT *, 
DATEDIFF(MINUTE,ISNULL(LAG(Emp_out) OVER(PARTITION BY Date, Room ORDER BY Emp_In),'00:00'),Emp_In) [Difference in Min]
FROM Your_table

我的示例脚本和结果

create table #temp(empdate date, empId int, room varchar(100), InTime time, outTime time)
insert into #temp Values ('2022-09-02','101','Room 1','04:30','05:00')
insert into #temp Values ('2022-09-02','102','Room 1','05:25','05:42')
insert into #temp Values ('2022-09-02','103','Room 1','05:48','07:00')
insert into #temp Values ('2022-09-02','101','Room 2','05:00','05:17')
insert into #temp Values ('2022-09-02','102','Room 2','05:36','05:48')
insert into #temp Values ('2022-09-02','103','Room 2','05:51','06:00')
SELECT *, 
DATEDIFF(MINUTE,ISNULL(LAG(outTime) OVER(PARTITION BY Room ORDER BY InTime),'00:00'),Intime) [Difference in Min]
FROM #temp
DROP TABLE #temp

输出：

empdate    empId       room       InTime           outTime          Difference in Min
---------- ----------- ---------- ---------------- ---------------- -----------------
2022-09-02 101         Room 1     04:30:00.0000000 05:00:00.0000000 270
2022-09-02 102         Room 1     05:25:00.0000000 05:42:00.0000000 25
2022-09-02 103         Room 1     05:48:00.0000000 07:00:00.0000000 6
2022-09-02 101         Room 2     05:00:00.0000000 05:17:00.0000000 300
2022-09-02 102         Room 2     05:36:00.0000000 05:48:00.0000000 19
2022-09-02 103         Room 2     05:51:00.0000000 06:00:00.0000000 3

Answer 2

创建表

CREATE TABLE DEMOLAG (Date Datetime , EMP_ID INT , Room Varchar(50), EMP_IN TIME(0) , EMP_OUT TIME(0))

插入记录

INSERT INTO DEMOLAG VALUES ('9/1/22',001,'ROOM 1', '04:30','05:00')
INSERT INTO DEMOLAG VALUES ('9/1/22',002,'ROOM 1', '05:25','05:42')
INSERT INTO DEMOLAG VALUES ('9/1/22',003,'ROOM 1', '05:48','06:13')
INSERT INTO DEMOLAG VALUES ('9/1/22',001,'ROOM 2', '05:00','05:17')
INSERT INTO DEMOLAG VALUES ('9/1/22',002,'ROOM 2', '05:36','05:48')
INSERT INTO DEMOLAG VALUES ('9/1/22',003,'ROOM 2', '05:51','06:05')

使用LEG功能实现

SELECT *,
ISNULL((LAG(EMP_OUT,1) OVER(Partition by Room ORDER BY EMP_OUT,Room,Emp_ID ASC)),'00:00') AS Prvempout, 
DATEDIFF(Minute,ISNULL((LAG(EMP_OUT,1) OVER(Partition by Room ORDER BY EMP_OUT,Room,Emp_ID ASC)),'00:00'),EMP_IN) AS Timediff 
FROM DEMOLAG

有关更多详细说明，请参阅在SQL 中使用LAG函数