I have login table structure as below
EmpId Logdate status 101 2021-04-01 06:54:35.000 in 101 2021-04-01 11:54:35.000 out 101 2021-04-01 15:54:35.000 in 101 2021-04-01 23:54:35.000 out 102 2021-04-01 06:54:35.000 in 102 2021-04-01 12:54:35.000 in 102 2021-04-01 11:54:35.000 out
I want to get like
EmpId Logdate in out 101 2021-04-01 2021-04-01 06:54:35.000 2021-04-01 11:54:35.000 101 2021-04-01 2021-04-01 15:54:35.000 2021-04-01 23:54:35.000 102 2021-04-01 2021-04-01 12:54:35.000 2021-04-01 11:54:35.000
Advertisement
Answer
As mentioned in my comment, I guess the last row in your result has the wrong login time. However, have a look on this approach:
DECLARE @t TABLE( EmpID int, LogDate datetime, stat char(3) ) INSERT INTO @t VALUES (101,'2021-04-01 06:54:35.000','in') ,(101,'2021-04-01 11:54:35.000','out') ,(101,'2021-04-01 15:54:35.000','in') ,(101,'2021-04-01 23:54:35.000','out') ,(102,'2021-04-01 06:54:35.000','in') ,(102,'2021-04-01 12:54:35.000','in') ,(102,'2021-04-01 11:54:35.000','out') ;WITH cteOut AS( SELECT * FROM @t WHERE stat = 'out' ), cteFilter AS( SELECT o.*, i.LogDate AS LogDateIn, ROW_NUMBER() OVER (PARTITION BY i.EmpID, o.LogDate ORDER BY i.LogDate DESC) AS srt FROM cteOut o LEFT JOIN @t i ON i.stat = 'in' AND i.EmpId = o.EmpId AND i.LogDate < o.LogDate ) SELECT EmpId, LogDateIn, LogDate AS LogDateOut FROM cteFilter WHERE srt = 1