4

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? – Nikita Aug 9 at 15:18
  • 3
    And what happens when someone comes in before midnight but leaves after midnight? – SMor Aug 9 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 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 at 8:52
6

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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