I have a dataset that I need to merge on overlapping date range to form a timeline to indicate Active and Not Active stages of a patient. I'm looking for a t-sql script that could transform this data.
This is a sample dataset:
+----------+-------+------------------+----------------+-------------+------------+
| Practice | PatID | AgeAtActiveStart | AgeAtActiveEnd | ActiveStart | ActiveEnd |
+----------+-------+------------------+----------------+-------------+------------+
| Prac1 | P1 | 58 | 72 | 2003-05-09 | 2017-12-22 |
| Prac1 | P1 | 59 | 60 | 2004-03-30 | 2005-03-30 |
| Prac1 | P1 | 60 | 63 | 2005-12-14 | 2008-04-29 |
| Prac1 | P1 | 63 | 65 | 2008-07-31 | 2010-06-17 |
| Prac1 | P1 | 65 | 68 | 2010-07-30 | 2013-08-07 |
| Prac1 | P1 | 69 | 72 | 2014-03-06 | 2018-01-04 |
| Prac1 | P2 | 32 | 33 | 2004-12-08 | 2006-04-01 |
| Prac1 | P2 | 32 | 33 | 2005-07-18 | 2006-07-18 |
| Prac1 | P2 | 33 | 34 | 2006-11-02 | 2007-11-02 |
| Prac1 | P2 | 36 | 38 | 2009-05-13 | 2011-02-09 |
| Prac1 | P2 | 38 | 39 | 2011-04-07 | 2012-06-16 |
| Prac1 | P2 | 40 | 41 | 2012-12-12 | 2014-11-16 |
| Prac1 | P2 | 41 | 45 | 2014-09-15 | 2017-12-21 |
+----------+-------+------------------+----------------+-------------+------------+
This is what I want to transform it into. In a timeline for each patient:
+----------+-------+------------------+----------------+-------------+------------+-----------+
| Practice | PatID | AgeAtActiveStart | AgeAtActiveEnd | ActiveStart | ActiveEnd | Status |
+----------+-------+------------------+----------------+-------------+------------+-----------+
| Prac1 | P1 | 58 | 72 | 2003-05-09 | 2018-01-04 | Active |
| Prac1 | P2 | 32 | 33 | 2004-12-08 | 2006-07-18 | Active |
| Prac1 | P2 | 33 | 33 | 2006-07-19 | 2006-11-01 | NotActive |
| Prac1 | P2 | 33 | 34 | 2006-11-02 | 2007-11-02 | Active |
| Prac1 | P2 | 34 | 36 | 2007-11-03 | 2009-05-12 | NotActive |
| Prac1 | P2 | 36 | 38 | 2009-05-13 | 2011-02-09 | Active |
| Prac1 | P2 | 38 | 38 | 2011-02-10 | 2011-04-06 | NotActive |
| Prac1 | P2 | 38 | 39 | 2011-04-07 | 2012-06-16 | Active |
| Prac1 | P2 | 39 | 40 | 2012-06-17 | 2012-12-11 | NotActive |
| Prac1 | P2 | 40 | 45 | 2012-12-12 | 2017-12-21 | Active |
+----------+-------+------------------+----------------+-------------+------------+-----------+
Appreciate any help with this. Many thanks.