Skip to content
Advertisement

Get multiple clock in and out time from multiple inputs and neglect without clock [closed]

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
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement