Sequencing events
Sequencing events
If you have data from two or more sources, such as Admissions and Emergency Department data in this example, it is often useful to sequence the events to assist in piecing together the patients' journeys.
Consider the below combination of the admission and separation dates from the 'Episode' table, with the presentation date from the 'Presentation' table. The data has been sorted by 'id' and 'start_date' (representing either the presentation date to an ED or the admission date to hospital).
Emergency Department (ED) Presentation data + Admissions Episode data combined
| id | source | ep_num | facility | start_date | end_date |
| 1 | ed | 1 | CAT | 2014-03-12 | |
| 1 | ed | 2 | ARD | 2014-03-13 | |
| 1 | admit | 1 | ARD | 2014-03-13 | 2014-03-13 |
| 1 | admit | 2 | CAT | 2014-03-13 | 2014-03-16 |
| 2 | admit | 1 | ROD | 2014-11-19 | 2014-12-03 |
| 2 | ed | 1 | CAT | 2015-03-22 | |
| 2 | admit | 2 | CAT | 2015-03-22 | 2015-03-28 |
| 4 | ed | 1 | NEU | 2014-04-01 | |
| 4 | ed | 2 | NEU | 2014-04-04 | |
| 4 | admit | 1 | NEU | 2014-04-04 | 2014-04-04 |
Stacking the data vertically like this allows for easily following the path between an ED presentation and a subsequent hospital admission going down the page within each 'id'.
It also allows you to quickly identify circumstances where the data may not align perfectly as expected. Consider 'id' number '1':
The patient had two hospital admission episodes, firstly at 'ARD' hospital, then a subsequent episode at 'CAT' hospital after being transferred between hospitals. While the 'ARD' ED presentation flows logically from ED-to-Admission on the same day, there is an ED presentation to 'CAT' which is mis-sequenced and does not logically occur between the transfer out from 'ARD' and the transfer in to 'CAT'.