I am using SQL Server 2016
I have a table that contains 1 row per month that a patient is assigned to a particular Provider.
A patient can be assigned to multiple providers during the year.
How can I derive date spans (startdate & enddate) to represent the time a patient was assigned to each provider.
My table looks like this:
+----------+---------------+------------+-----------+
| Provider | Patient | StartDate | EndDate |
+----------+---------------+------------+-----------+
| 1922157 | 12345 | 20191201 | 20191231 |
| 1904176 | 12345 | 20191101 | 20191201 |
| 1904176 | 12345 | 20191001 | 20191101 |
| 1904176 | 12345 | 20190901 | 20191001 |
| 1904176 | 12345 | 20190801 | 20190901 |
| 1904176 | 12345 | 20190701 | 20190801 |
| 1904176 | 12345 | 20190601 | 20190701 |
| 1904176 | 12345 | 20190501 | 20190601 |
| 1904176 | 12345 | 20190401 | 20190501 |
| 1904176 | 12345 | 20190301 | 20190401 |
| 1904176 | 12345 | 20190201 | 20190301 |
| 1922157 | 12345 | 20190101 | 20190201 |
| 1922157 | 56789 | 20190101 | 20190201 |
+----------+---------------+------------+-----------+
In this case, patient 12345 was assigned to 2 different providers. One for 2 months, January and then December and the other for the rest of the year (10 months) February through November. Patient 56789 was only assigned to 1 provider (1922157) for 1 month (in December).
I’m trying to make it so my output looks like the below table but I am running into issues I think because the patient is assigned to the same pcp during 2 different times of the year. I tried using the lag function but I only get the correct results for some cases but not all such as this particular case.
+----------+---------------+------------+-----------+
| Provider | Patient | StartDate | EndDate |
+----------+---------------+------------+-----------+
| 1922157 | 12345 | 20190101 | 20190201 |
| 1904176 | 12345 | 20190201 | 20191201 |
| 1922157 | 12345 | 20191201 | 20191231 |
| 1922157 | 56789 | 20191201 | 20191231 |
+----------+---------------+------------+-----------+
Go to Source
Author: Juan Velez