Calculate the difference in minutes between 'First-row' and 'Second-row' with 2-CodePudding

I have the following table in which I have to calculate the difference in minutes between 'First-row' Emp_Out and 'Second-row' Emp_IN for each room and for each day starting from 12 am.

Table:

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

Can LAG be used for it or I'm missing a logic which can be helped?

CodePudding user response：

Use LAG to get the next row value, partition the rows using Room

The first entry of the day for the room will get Null and replace that with '00:00' which means 12 AM.

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

My sample scripts and the result

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

Output:

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

CodePudding user response：

Create the table

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

Insert the Records

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')

USE LEG Function to achieve

    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

For more detail explanation you can refer to Use LAG Function in SQL