Merging Entry & Leaving Data
【Question】
Here are access control records:
Name Action Time
John Enter 2015-05-13 9:00
John Leave 2015-05-13 11:00
John Enter 2015-05-13 12:00
John Leave 2015-05-13 13:00
I need to merge the data as follows:
Name EntryTime LeavingTime
John 2015-05-13 9:00 2015-05-13 11:00
John 2015-05-13 12:00 2015-05-13 13:00
Some records don’t have entry data or leaving data.
【Answer】
It’s not easy to do an order-based operation in SQL, especially when there is missing data. There will be errors if we just create index numbers for the records. We can do this in SPL with grouping intervals function:
| A |
|
| 1 |
$select Name,Action,Time from tb order by Name,Time |
| 2 |
=a=0 |
| 3 |
=A1.group@o(Name,a+=if(Action=="Entry" || Action[-1]=="Leave",1,0)) |
| 4 |
=A3.new(Name, (t=~.align(["Enter","Leave"],Action).(Time))(1):EntryTime,t(2):LeavingTime) |
A1: Retrieve data in SQL and ordered by Name and Time;
A3: First group data by Name and then re-group it by comparing the current action value and the last action value to get groups of enter vs leave;
A4: Create a new result set according to A3, align ["Enter","Leave"] to each group, and get the first time data as the enter time and the second data as the leave time.
Suppose the access data as follows:
Name Action Time
John Enter 2015-05-13 09:00:00
John Leave 2015-05-13 11:00:00
John Enter 2015-05-13 12:00:00
John Leave 2015-05-13 13:00:00
Jack Enter 2015-05-13 09:00:00
Jack Enter 2015-05-13 12:00:00
Jack Leave 2015-05-13 13:00:00
Jack Leave 2015-05-13 14:00:00
Jack Enter 2015-05-13 15:00:00
James Leave 2015-05-13 09:00:00
James Enter 2015-05-13 11:00:00
James Leave 2015-05-13 13:00:00
The result:
| Index |
Name |
EntryTime |
LeavingTime |
| 1 |
John |
2015-05-13 09:00:00 |
2015-05-13 11:00:00 |
| 2 |
John |
2015-05-13 12:00:00 |
2015-05-13 13:00:00 |
| 3 |
Jack |
2015-05-13 09:00:00 |
(null) |
| 4 |
Jack |
2015-05-13 12:00:00 |
2015-05-13 13:00:00 |
| 5 |
Jack |
(null) |
2015-05-13 14:00:00 |
| 6 |
Jack |
2015-05-13 15:00:00 |
(null) |
| 7 |
James |
(null) |
2015-05-13 09:00:00 |
| 8 |
James |
2015-05-13 11:00:00 |
2015-05-13 13:00:00 |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
