Converting multiple rows to one row

Question

I have the following data coming from Finger Print devices, Each In and Out of employees are recorded.

UserId CheckTime CheckType
------ ----------- -----------
 2 2020年08月03日 08:15:12.053 I
 2 2020年08月03日 16:00:00.053 O
 2 2020年08月04日 08:00:12.053 I
 2 2020年08月04日 16:10:00.053 O

I want the above data to be as following:

UserId CheckTime CommingTime LeavingTime
------ ----------- ----------- -------------
 2 2020年08月03日 08:15:12.053 16:00:00.053
 2 2020年08月04日 08:00:12.053 16:10:00.053

**Note: ** A user can have multiple In Out record per day

I have tried the following answers but didn't help:

How to merge multiple rows into one row with SQL?

Any idea??

• NikitaSerbskiy: is it possible that you may have no corresponding Out records for some In records and vice versa? what version of database server do you use?
• SMor: And what happens when someone comes in before midnight but leaves after midnight?
• SMor: How is this (ignoring the noted complications) not simply the minimum of the "in" rows and the maximum of the "out" rows with the appropriate group by clause?
• Andriy M: @SMor: Seems to me like a valid option to suggest. Keep in mind that comments can't bring you rep points, but answers can (wink, wink, nudge, nudge).

Answer 1

Main idea:

WITH cte AS ( SELECT UserId, 
 CheckTime, 
 LEAD(CheckTime) OVER (PARTITION BY UserId ORDER BY CheckTime) NextTime, 
 CheckType, 
 LEAD(CheckType) OVER (PARTITION BY UserId ORDER BY CheckTime) NextType )
SELECT UserId, CheckTime, NextTime
FROM cte
WHERE CheckType = 'I'
 AND NextType = 'O'

Modify as you need - extract date and/or time parts, check that date parts are equal, etc.