Working with healthcare data in SQL and I'm trying to fill in records when there are overlapping dates for thousands of records. This mostly occurs when a patient is in a nursing home and then transferred to a hospital. They have not been discharged yet from the nursing home because their bed is being kept available for them, so that's why the discharge date is missing. Here's one example of what the records look like:
| Patient | Admission_Date | Discharge_Date | Location |
| ------------ | ------------ | ------------ | ------------ |
| ABC | 1/2/2021 | MISSING | NURSING HOME |
| ABC | 2/3/2021 | 2/4/2021 | ICU |
| ABC | 4/10/2021 | 4/13/2021 | HOSPITAL |
Here's what I need it to look like:
Patient | Admission_Date | Discharge_Date | Location |
---|---|---|---|
ABC | 1/2/2021 | 2/3/2021 | NURSING HOME |
ABC | 2/3/2021 | 2/4/2021 | ICU |
ABC | 2/4/2021 | 4/10/2021 | NURSING HOME |
ABC | 4/10/2021 | 4/13/2021 | HOSPITAL |
ABC | 4/13/2021 | MISSING | NURSING HOME |
Any ideas on how to get this done?
I've tried using a combination of multiple CASE WHEN statements and lag/leads but it's obviously very inefficient and difficult to account for all of the possible records between nursing home stays.
1条答案
按热度按时间u7up0aaq1#
For the exact example from above the solution is here. I haven't tested it for multiple patients or multiple missing dates. But I can, if it's not enough.
You can try it here: https://sqlize.online/sql/mssql2022/14e363a81956b2e9019fc44daf47a8c7/
Look at this select for the Idea of a new column NEXT_Admission_Date: