Consider a database table holding data like that:
Id | UserId | IsCheckIn | DateTime | Image | AccountId |
---|---|---|---|---|---|
30356 | 60866 | 1 | 2020-12-19 12:17:17 | b622f3e0806f.jpg | 10017 |
30355 | 60866 | 0 | 2020-12-19 10:52:26 | b622f3e0806f.jpg | 10017 |
30354 | 60866 | 1 | 2020-12-19 10:51:02 | b622f3e0806f.jpg | 10017 |
30353 | 60866 | 0 | 2020-01-20 09:29:42 | 1596.jpg | 10017 |
Desired output :
Id | UserId | IsCheckIn | InDateTime | InImage | AccountId | IsCheckOut | OutDateTime | OutImage |
---|---|---|---|---|---|---|---|---|
30356 | 60866 | 1 | 2020-12-19 12:17:17 | b622f3e0806f.jpg | 10017 | 0 | 2020-12-19 10:52:26 | b622f3e0806f.jpg |
30354 | 60866 | 1 | 2020-12-19 10:51:02 | b622f3e0806f.jpg | 10017 | 0 | 2020-01-20 09:29:42 | 1596.jpg |
Please help
Thanks in advance….
Advertisement
Answer
If checkins and checkouts always properly interleave, you can use lead()
:
x
select id, userid, ischeckin,
datetime as indatetime, image as inimage,
accountid,
lead_datetime as outdatetime, lead_image as outimage
from (
select t.*,
lead(datetime) over(partition by userid, accountid order by datetime) as lead_datetime,
lead(image) over(partition by userid, accountid order by datetime) as lead_image
from mytable t
) t
where ischeckin = 0