I have a problem with complex requirements, I'm hoping I can get some help with solving this. My SQL knowledge is very basic, and I have no idea how to solve this problem. Currently, I have an Events
table structured as follows:
ID | Name | Time | Event Type
----------------------------------------------------------
133000 | Elise | 2016-02-17 06:39:42.000 | Arrival
133000 | Elise | 2016-02-18 06:20:22.000 | Arrival
133000 | Elise | 2016-02-18 20:43:46.000 | Departure
133020 | Elise | 2016-02-19 06:29:46.000 | Arrival
133445 | Peter | 2016-02-01 20:09:00.000 | Departure
133445 | Peter | 2016-02-02 06:32:02.000 | Arrival
133445 | Peter | 2016-02-02 17:03:04.000 | Departure
133445 | Peter | 2016-02-02 19:44:06.000 | Arrival
133445 | Peter | 2016-02-02 19:56:56.000 | Departure
Now, I want to query this data in a way so that it is structured this way:
ID | Name | Arrival | Departure
----------------------------------------------------------
133000 | Elise | 2016-02-17 06:39:42.000 | NULL
133000 | Elise | 2016-02-18 06:20:22.000 | 2016-02-18 20:43:46.000
133000 | Elise | 2016-02-19 06:29:46.000 | NULL
133445 | Peter | NULL | 2016-02-01 20:09:00.000
133445 | Peter | 2016-02-02 06:32:02.000 | 2016-02-02 17:03:04.000
133445 | Peter | 2016-02-02 19:44:06.000 | 2016-02-02 19:56:56.000
In other words, I have two new columns: Arrival
and Departure
. Then for each person in the table, apply the following logic in chronological order:
- If the
Event Type
isArrival
, it should be mapped to a new row with theTime
value in theArrival
column. - If the
Event Type
isDeparture
, check to see if the previous row is alsoDeparture
. If so, it should be mapped to a new row with theTime
value in theDeparture
column, andArrival
is null. If not, just transfer theTime
value into theDeparture
column of the previous row.
It is best if that can be done via a SQL Query, but a function is fine too. I am using MS SQL Server. Thanks!