patternsqlMinor
Converting multiple rows to one row
Viewed 0 times
rowsonemultipleconvertingrow
Problem
I have the following data coming from Finger Print devices, Each In and Out of employees are recorded.
I want the above data to be as following:
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??
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 OI 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.053Note: 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:
Modify as you need - extract date and/or time parts, check that date parts are equal, etc.
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.