HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Converting multiple rows to one row

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
rowsonemultipleconvertingrow

Problem

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

Solution

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.

Code Snippets

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'

Context

StackExchange Database Administrators Q#273465, answer score: 6

Revisions (0)

No revisions yet.