Mysql Question posted in
The official MySQL documentation can be found here.

union and group biometrics logs in mysql database

I have this table from fingerprint sensor and I need to show the result in one row

ID |  DateTime                | Flag

41 |  2017-02-22 08:05:56.000 | I

41 |  2017-02-22 18:11:03.000 | O

41 |  2017-02-23 23:45:56.000 | I

41 |  2017-02-24 08:11:03.000 | O

Result needed like this:

ID | IN-DateTime | OUT-DateTime

41 | 2017-02-22 08:05:56.000 | 2017-02-22 18:11:03.000

41 | 2017-02-23 23:45:56.000 | 2017-02-24 08:11:03.000

Tags:

2

Answers


  1. 0

    Assuming that in and out records would always be paired, we can try the following approach using ROW_NUMBER():

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, Flag ORDER BY DateTime) rn
    FROM yourTable
)

SELECT
    ID,
    MAX(CASE WHEN Flag = 'I' THEN DateTime END) AS "IN-DateTime",
    MAX(CASE WHEN Flag = 'O' THEN DateTime END) AS "OUT-DateTime"
FROM cte
GROUP BY ID, rn
ORDER BY ID;

    Note: The above will only work on MySQL 8+, which has the ROW_NUMBER() window function available.

    Login or Signup to reply.
  2. 0

    Take next event datetime for rows with flag ‘I’.
    Then filter rows with flag ‘I’.
    Considered IN without OUT.

    See example

    D DateTime Flag
    41 2017-02-22 08:05:56 I
    41 2017-02-22 18:11:03 O
    41 2017-02-23 23:45:56 I
    41 2017-02-24 08:11:03 O
    42 2017-02-24 08:12:03 I
    42 2017-02-24 08:12:05 I
    42 2017-02-24 08:12:08 O 
    select *
from(
  select *
    ,case when flag='I' then
      case when lead(flag)over(partition by d order by datetime)='O'
          then lead(datetime)over(partition by d order by datetime)
      end
     else datetime
    end outtime
  from test
)t
where flag='I'
    D DateTime Flag outtime
    41 2017-02-22 08:05:56 I 2017-02-22 18:11:03
    41 2017-02-23 23:45:56 I 2017-02-24 08:11:03
    42 2017-02-24 08:12:03 I null
    42 2017-02-24 08:12:05 I 2017-02-24 08:12:08

    fiddle

    More wired case:

    IN without OUT and OUT without IN

    D DateTime Flag
    41 2017-02-22 08:05:56 I
    41 2017-02-22 18:11:03 O
    41 2017-02-23 23:45:56 I
    41 2017-02-24 08:11:03 O
    42 2017-02-24 08:12:03 I
    42 2017-02-24 08:12:05 I
    42 2017-02-24 08:12:08 O
    43 2017-02-24 08:12:08 I
    43 2017-02-24 08:12:18 O
    43 2017-02-24 08:12:28 O
    43 2017-02-24 08:12:38 I
    43 2017-02-24 08:12:48 O 
    select d
  ,case when flag='I' then datetime
   end IN_datetime
  ,case when flag='O' then datetime 
   else outtime
   end OUT_datetime
from(
  select *
    ,case when flag='I' then
      case when lead(flag)over(partition by d order by datetime)='O'
          then lead(datetime)over(partition by d order by datetime)
      end
     else 
        case when lag(flag)over(partition by d order by datetime)='I'
          then lag(datetime)over(partition by d order by datetime)
      end
    end outtime
  from test
)t
where flag='I'
 or(flag='O' and outtime is null)
    d IN_datetime OUT_datetime
    41 2017-02-22 08:05:56 2017-02-22 18:11:03
    41 2017-02-23 23:45:56 2017-02-24 08:11:03
    42 2017-02-24 08:12:03 null
    42 2017-02-24 08:12:05 2017-02-24 08:12:08
    43 2017-02-24 08:12:08 2017-02-24 08:12:18
    43 null 2017-02-24 08:12:28
    43 2017-02-24 08:12:38 2017-02-24 08:12:48

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search