I’m trying to pull test entries that had happened after a patient’s stay in a special care hospital based on a date range looking at the first patient stay in that period. The patient can have a number of stay episodes and tests through those stays. There isn’t really a definitive ID that links a patient & stay episode to the test other than dates.
The problem with that is there are potentially more than one test spanning the stay period, and I can’t look for the MIN stay date as those tests may be related to earlier stays. So I need to find the next test date directly after the first date of the date range of the stay I’m filtering for.
The filter may cross a previous stay ending in that period but would be looking for the first stay date found directly after the date filter, then the first test date after the first stay date found.
I’ve created a simplified example below with the patient stays, test 1 & 2 data and the resultant output.
Main patient entry/exit data set
IdentityID Entrance Date Exit Date
=========== ============ =========
10001 10/04/2014 08/03/2017
10001 12/05/2017 08/10/2019
10001 03/02/2020 12/08/2021
10002 16/03/2016 05/04/2017
10002 16/06/2017 09/04/2018
10003 12/05/2013 12/05/2015
10003 08/06/2018 NULL
- Filter entries in entry/exit data set by date range between entrance date = 01/07/2015 to exit date = 31/12/2023, with first episode in date filter range returned.
- Show first test from test 1 and test 2 data set after the first entry date found in entry/exit data set,
if exit date = null, still include the first test in that date range based on the test date found after the first entrance date.
Test data set 1
Test1 Date IdentityID
========== ==========
01/05/2015 10001
10/05/2016 10001
10/02/2017 10001
12/09/2017 10001
18/03/2018 10001
14/05/2020 10001
16/08/2016 10002
18/06/2018 10002
09/08/2019 10002
16/08/2020 10002
12/05/2018 10003
22/07/2018 10003
11/09/2018 10003
18/08/2020 10003
14/08/2021 10003
08/09/2022 10003
Test data set 2
Test2 Date IdentityID
========== ==========
01/05/2015 10001
12/05/2016 10001
10/04/2017 10001
18/03/2018 10001
14/05/2019 10001
04/02/2016 10002
06/08/2016 10002
18/06/2018 10002
09/08/2019 10002
16/08/2020 10002
12/05/2018 10003
28/08/2018 10003
11/09/2018 10003
18/08/2020 10003
18/09/2021 10003
08/09/2022 10003
Output would be:
IdentityID Entrance Date Exit Date Test1 Date Test2 Date IdentityID
=========== ============ ========== ========== ========== ==========
10001 12/05/2017 08/10/2019 12/09/2017 18/03/2018 10001
10002 16/03/2016 05/04/2017 16/08/2016 06/08/2016 10002
10003 08/06/2018 NULL 22/07/2018 28/08/2018 10003
I tried using a CTE table to get each test date after the initial stay date, it seemed to work for some of them but no others and have limited experience with CTEs, so wasn’t confident as it got more complex.
I’d appreciate any pointers/suggestions how to go about this. My data includes a few more test tables but if I can get a logic to build the first section that would be a great help.
Thanks