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??

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.