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

  • 2
    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? Aug 9, 2020 at 15:18
  • 3
    And what happens when someone comes in before midnight but leaves after midnight?
    – SMor
    Aug 9, 2020 at 15:39
  • 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?
    – SMor
    Aug 9, 2020 at 18:40
  • @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).
    – Andriy M
    Aug 10, 2020 at 8:52

1 Answer 1


Main idea:

WITH cte AS ( SELECT UserId, 
                     LEAD(CheckTime) OVER (PARTITION BY UserId ORDER BY CheckTime) NextTime, 
                     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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.