SQL Syntax HELP on merging enrollment spans

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 19, 2018 at 2:59 pm

  #354525

  If object_id('tempdb..#SPAN1') IS NOT NULL DROP TABLE #SPAN1;
If object_id('tempdb..#SPAN2') IS NOT NULL DROP TABLE #SPAN2;

  CREATE table #SPAN1( name VARCHAR(20), SpStart VARCHAR(8), SpEnd VARCHAR(8) );
CREATE table #SPAN2( name VARCHAR(20), SpStart VARCHAR(8), SpEnd VARCHAR(8) );

  INSERT INTO #SPAN1( name,SpStart,SpEnd) VALUES ( 'mez', '20170101', '20181231' );
INSERT INTO #SPAN2( name,SpStart,SpEnd) VALUES ( 'mez', '20170601', '20170630' );

  /*
    I need to com up with 3 spans here.
    1. 20170101 to 20170601
    2. 20170601 to 20170630
    3. 20170630 to 20181231
    How do we generate the SQL - Sorry I am stuck.. My trying didnt work out well
*/

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 19, 2018 at 3:04 pm

  #1998316

  Why do you need two separate temp tables?   With just one table, the process is easier...  The question is whether or not all the spans are contiguous to each other...

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 20, 2018 at 6:43 am

  #1998374

  Spans may not be continuous. But I can take care of that later ( issue #2 ) For now I just need the breakdown of spans.

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  July 20, 2018 at 7:36 am

  #1998386

  000I notice you sidestepped the question about the two temp tables.  Here is a method.  The first CTE is simply to combine your two temp tables into one.

  WITH span AS
(-- Combine the two tables
SELECT s.[name], s.SpStart, s.SpEnd
FROM #SPAN1 s
UNION ALL
SELECT s.[name], s.SpStart, s.SpEnd
FROM #SPAN2 s
)
, span_dates AS
(
SELECT s.[name], d.dt start_dt, LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt
FROM span s
CROSS APPLY ( VALUES(s.SpStart), (s.SpEnd) ) d(dt)
)
SELECT sd.[name], sd.start_dt, sd.end_dt
FROM span_dates sd
WHERE sd.end_dt IS NOT NULL

  This first "unpivots" the dates and then uses LEAD (introduced in SQL 2012) to find the ranges.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 20, 2018 at 9:12 am

  #1998432

  drew.allen - Friday, July 20, 2018 7:36 AM

  000I notice you sidestepped the question about the two temp tables.  Here is a method.  The first CTE is simply to combine your two temp tables into one.

  WITH span AS
(-- Combine the two tables
SELECT s.[name], s.SpStart, s.SpEnd
FROM #SPAN1 s
UNION ALL
SELECT s.[name], s.SpStart, s.SpEnd
FROM #SPAN2 s
)
, span_dates AS
(
SELECT s.[name], d.dt start_dt, LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt
FROM span s
CROSS APPLY ( VALUES(s.SpStart), (s.SpEnd) ) d(dt)
)
SELECT sd.[name], sd.start_dt, sd.end_dt
FROM span_dates sd
WHERE sd.end_dt IS NOT NULL

  This first "unpivots" the dates and then uses LEAD (introduced in SQL 2012) to find the ranges.

  Drew

  Thank you that works ....  Now I have to learn some of the new functions ( LEAD ) ...  Anyway thx

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 23, 2018 at 1:04 pm

  #1998662

  mw_sql_developer - Friday, July 20, 2018 9:12 AM

  drew.allen - Friday, July 20, 2018 7:36 AM

  000I notice you sidestepped the question about the two temp tables.  Here is a method.  The first CTE is simply to combine your two temp tables into one.

  WITH span AS
(-- Combine the two tables
SELECT s.[name], s.SpStart, s.SpEnd
FROM #SPAN1 s
UNION ALL
SELECT s.[name], s.SpStart, s.SpEnd
FROM #SPAN2 s
)
, span_dates AS
(
SELECT s.[name], d.dt start_dt, LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt
FROM span s
CROSS APPLY ( VALUES(s.SpStart), (s.SpEnd) ) d(dt)
)
SELECT sd.[name], sd.start_dt, sd.end_dt
FROM span_dates sd
WHERE sd.end_dt IS NOT NULL

  This first "unpivots" the dates and then uses LEAD (introduced in SQL 2012) to find the ranges.

  Drew

  Thank you that works ....  Now I have to learn some of the new functions ( LEAD ) ...  Anyway thx

  First of all thanks....
  Got a slight issue...
  Now when we break the spans I would like include the value in the RSN column to appear we well. That way we know if there was a new span created I would like to know to which original span it belonged to ...  You will understand what i mean when you run the SQL. The output in the 3rd line is wrong. It says "PCP CHANGE" under the RSN column, when in fact it should say "PARENT SPAN" ( Why ? Because it belongs to the original span ) 

  
If object_id('tempdb..#SPAN1') IS NOT NULL DROP TABLE #SPAN1;
If object_id('tempdb..#SPAN2') IS NOT NULL DROP TABLE #SPAN2;

  CREATE table #SPAN1( name VARCHAR(20), SpStart VARCHAR(8), SpEnd VARCHAR(8), RSN VARCHAR(100) );
CREATE table #SPAN2( name VARCHAR(20), SpStart VARCHAR(8), SpEnd VARCHAR(8), RSN VARCHAR(100) );

  INSERT INTO #SPAN1( name,SpStart,SpEnd, RSN) VALUES ( 'mez', '20170101', '20181231','PARENT SPAN' );
INSERT INTO #SPAN2( name,SpStart,SpEnd, RSN) VALUES ( 'mez', '20170601', '20170630','PCP CHANGE' );

  WITH span AS
( -- Combine the two tables
 SELECT s.[name], s.SpStart, s.SpEnd, s.RSN
 FROM #SPAN1 s
 UNION ALL
 SELECT s.[name], s.SpStart, s.SpEnd, s.RSN
 FROM #SPAN2 s
)
, span_dates AS
(
 SELECT s.[name], d.dt start_dt, LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt, s.RSN
 FROM span s
 CROSS APPLY ( VALUES(s.SpStart), (s.SpEnd) ) d(dt)
)
SELECT sd.[name], sd.start_dt, sd.end_dt, sd.RSN
FROM span_dates sd
WHERE sd.end_dt IS NOT NULL

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 23, 2018 at 2:02 pm

  #1998678

  Well, I came up with my own solution.. Not fully tested 100%. I have a feeling it may work for all cases. But it worked in this case...

  
WITH span AS
( -- Combine the two tables
 SELECT s.[name], s.SpStart, s.SpEnd, s.RSN
 FROM #SPAN1 s
 UNION ALL
 SELECT s.[name], s.SpStart, s.SpEnd, s.RSN
 FROM #SPAN2 s
)
, span_dates AS
(
 SELECT s.[name], d.dt start_dt,
 LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt,

   CASE
        WHEN LAG(s.rsn) OVER(PARTITION BY s.[name] ORDER BY s.SpStart) IS NULL THEN -- Make an exception for ROW #1 or earliest SPAN
            s.RSN
        ELSE
            LEAD(d.rsn) OVER(PARTITION BY s.[name] ORDER BY d.dt)
        END
    AS from_what_span
 FROM span s
 CROSS APPLY ( VALUES(s.SpStart, s.RSN), (s.SpEnd, s.RSN) ) d(dt,rsn)
)
SELECT sd.[name], sd.start_dt, sd.end_dt, sd.from_what_span
FROM span_dates sd
WHERE sd.end_dt IS NOT NULL

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  July 23, 2018 at 2:45 pm

  #1998689

  mw_sql_developer - Monday, July 23, 2018 1:04 PM

  First of all thanks....
  Got a slight issue...
  Now when we break the spans I would like include the value in the RSN column to appear we well. That way we know if there was a new span created I would like to know to which original span it belonged to ...  You will understand what i mean when you run the SQL. The output in the 3rd line is wrong. It says "PCP CHANGE" under the RSN column, when in fact it should say "PARENT SPAN" ( Why ? Because it belongs to the original span ) 

  You're moving the goal posts.  Nothing is more frustrating than someone who continually changes the requirements.  Do your homework up front and give as complete a description as you can of the problem.

  I haven't thoroughly reviewed your "solution", but I don't think it will work.  There are multiple solutions to this problem, and you said that you could handle gaps, so I gave you the very simplest solution available.  However, changing the goal posts indicates that the simplest solution is no longer viable, but I don't want to get sucked into your ever changing requirements, so you're on your own as far as I'm concerned.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 23, 2018 at 3:55 pm

  #1998698

  drew.allen - Monday, July 23, 2018 2:45 PM

  mw_sql_developer - Monday, July 23, 2018 1:04 PM

  First of all thanks....
  Got a slight issue...
  Now when we break the spans I would like include the value in the RSN column to appear we well. That way we know if there was a new span created I would like to know to which original span it belonged to ...  You will understand what i mean when you run the SQL. The output in the 3rd line is wrong. It says "PCP CHANGE" under the RSN column, when in fact it should say "PARENT SPAN" ( Why ? Because it belongs to the original span ) 

  You're moving the goal posts.  Nothing is more frustrating than someone who continually changes the requirements.  Do your homework up front and give as complete a description as you can of the problem.

  I haven't thoroughly reviewed your "solution", but I don't think it will work.  There are multiple solutions to this problem, and you said that you could handle gaps, so I gave you the very simplest solution available.  However, changing the goal posts indicates that the simplest solution is no longer viable, but I don't want to get sucked into your ever changing requirements, so you're on your own as far as I'm concerned.

  Drew

  No problem this point did not come to my mind at first. The solution you gave works fine.

• Nate the DBA

  SSC-Addicted

  Points: 430

  More actions

  July 24, 2018 at 10:14 am

  #1998834

  The "solution" only works if your "PARENT SPAN" has exactly zero or one "CHILD span" (i.e. just one date-span that is "inside it"; i.e. the parent span envelops just one child).  Observe:

  
IF object_id('tempdb..#SPANS') IS NOT NULL DROP TABLE #SPANS;
CREATE table #SPANS( name VARCHAR(20), SpStart VARCHAR(8), SpEnd VARCHAR(8), RSN VARCHAR(100) );

  --Simplified example by merging original 2 temp-tables into one
INSERT INTO #SPANS
VALUES( 'mez', '20170101', '20181231','PARENT SPAN' )  --a 2 year span
  , ( 'mez', '20170601', '20170630','PCP CHANGE' )  --month of June 2017
  , ( 'mez', '20180601', '20180630','PCP CHANGE 2' )  --month of June 2018
;

  WITH span_dates AS
(
SELECT s.[name], d.dt start_dt
  , LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt
  , CASE 
   WHEN LAG(s.rsn) OVER(PARTITION BY s.[name] ORDER BY s.SpStart) IS NULL -- Make an exception for ROW #1 or earliest SPAN
    THEN s.RSN
   ELSE
    LEAD(d.rsn) OVER(PARTITION BY s.[name] ORDER BY d.dt)
   END
   AS from_what_span
FROM #SPANS s
CROSS APPLY ( VALUES(s.SpStart, s.RSN), (s.SpEnd, s.RSN) ) d(dt,rsn)
)
SELECT sd.[name], sd.start_dt, sd.end_dt, sd.from_what_span
FROM span_dates sd
WHERE sd.end_dt IS NOT NULL
ORDER BY sd.[name], sd.start_dt;


  Result:
  
  The middle row is of course wrong; the dates between 20170630 and 20180601 should be marked as coming from "parent span".

  I'd highly recommend generating a calendar table (even if it's just temporary, like this) to "explode" the spans into day-by-day rows, assign the correct "from what span" monikers, then use simple aggregation to re-condense into what you want.

  The pseudo-code would look something like this:
  1. Generate #temp calendar table with 1 row for each date between the min & max of the PARENT span.
  2. Add a column to the #temp calendar table that indicates "from what span" each date comes from; default to "PARENT".
  3. For each CHILD span, assign a new value to your new "from what span" column, with that child's name, i.e. "PCP Change", "PCP Change 2", etc.  You could do this with a JOIN from #temp to Spans on #temp.Date between Span's Start and End.
  4. Final query: select name, min(date), max(date), "from what span" columns, from your #temp table, group by name & "from what span".

  Simple, no?  Not sure if I'm "reading between the lines" too much into your requirements, but given that your "Parent Span" spans 2 years and your "Child span" was only one month out of that, I figured you might see more than one "Child span" for a given Parent and need to deal with it correctly.

  Sidebar: this question piqued my interest because I used to work with education data and "enrollment" was a common term for dealing with student information system data.  Not sure if that's what you're working with too, but there you have it.  Good luck!

  -Nate the DBA natethedba.com

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 24, 2018 at 11:07 am

  #1998848

  Nate the DBA - Tuesday, July 24, 2018 10:14 AM

  The "solution" only works if your "PARENT SPAN" has exactly zero or one "CHILD span" (i.e. just one date-span that is "inside it"; i.e. the parent span envelops just one child).  Observe:

  
IF object_id('tempdb..#SPANS') IS NOT NULL DROP TABLE #SPANS;
CREATE table #SPANS( name VARCHAR(20), SpStart VARCHAR(8), SpEnd VARCHAR(8), RSN VARCHAR(100) );

  --Simplified example by merging original 2 temp-tables into one
INSERT INTO #SPANS
VALUES( 'mez', '20170101', '20181231','PARENT SPAN' )  --a 2 year span
  , ( 'mez', '20170601', '20170630','PCP CHANGE' )  --month of June 2017
  , ( 'mez', '20180601', '20180630','PCP CHANGE 2' )  --month of June 2018
;

  WITH span_dates AS
(
SELECT s.[name], d.dt start_dt
  , LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt
  , CASE 
   WHEN LAG(s.rsn) OVER(PARTITION BY s.[name] ORDER BY s.SpStart) IS NULL -- Make an exception for ROW #1 or earliest SPAN
    THEN s.RSN
   ELSE
    LEAD(d.rsn) OVER(PARTITION BY s.[name] ORDER BY d.dt)
   END
   AS from_what_span
FROM #SPANS s
CROSS APPLY ( VALUES(s.SpStart, s.RSN), (s.SpEnd, s.RSN) ) d(dt,rsn)
)
SELECT sd.[name], sd.start_dt, sd.end_dt, sd.from_what_span
FROM span_dates sd
WHERE sd.end_dt IS NOT NULL
ORDER BY sd.[name], sd.start_dt;


  Result:
  
  The middle row is of course wrong; the dates between 20170630 and 20180601 should be marked as coming from "parent span".  But no matter how hard we try, we probably can't get it to do that.  (Disclaimer: I'm no expert at windowing functions; they're cool, but they don't seem like the correct tool for this particular job -- but I could be dead wrong.)

  I'd highly recommend generating a calendar table (even if it's just temporary, like this) to "explode" the spans into day-by-day rows, assign the correct "from what span" monikers, then use simple aggregation to re-condense into what you want.

  The pseudo-code would look something like this:
  1. Generate #temp calendar table with 1 row for each date between the min & max of the PARENT span.
  2. Add a column to the #temp calendar table that indicates "from what span" each date comes from; default to "PARENT".
  3. For each CHILD span, assign a new value to your new "from what span" column, with that child's name, i.e. "PCP Change", "PCP Change 2", etc.  You could do this with a JOIN from #temp to Spans on #temp.Date between Span's Start and End.
  4. Final query: select name, min(date), max(date), "from what span" columns, from your #temp table, group by name & "from what span".

  Simple, no?  Not sure if I'm "reading between the lines" too much into your requirements, but given that your "Parent Span" spans 2 years and your "Child span" was only one month out of that, I figured you might see more than one "Child span" for a given Parent and need to deal with it correctly.

  Sidebar: this question piqued my interest because I used to work with education data and "enrollment" was a common term for dealing with student information system data.  Not sure if that's what you're working with too, but there you have it.  Good luck!

  Done! I was working on this issue this morning and caught it as well..  Here is a perfect code that works...

  
IF object_id('tempdb..#SPANS') IS NOT NULL DROP TABLE #SPANS;
CREATE table #SPANS( name VARCHAR(20), SpStart VARCHAR(8), SpEnd VARCHAR(8), RSN VARCHAR(100) );

  --Simplified example by merging original 2 temp-tables into one
INSERT INTO #SPANS
VALUES( 'mez', '20170101', '20181231','PARENT SPAN' ) --a 2 year span
, ( 'mez', '20170601', '20170630','PCP CHANGE' ) --month of June 2017
, ( 'mez', '20180601', '20180630','PCP CHANGE 2' ) --month of June 2018
;

  WITH
span_dates AS
(
SELECT s.[name], d.dt start_dt
, LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt
FROM #SPANS s
CROSS APPLY ( VALUES(s.SpStart, s.RSN), (s.SpEnd, s.RSN) ) d(dt,rsn)
)
,
spans_with_length as
(
    Select
    *, DATEDIFF(d, SpStart, SpEnd ) as SpanLength 
    FROM
    #SPANS s
)
SELECT sd.[name], sd.start_dt, sd.end_dt, B.RSN
FROM span_dates sd
CROSS APPLY --Find the parent span that covers the new spans and pick the span reason associated with the parent.
(
        --WHEN a new span is created ( call it a newborn span ), we like to know who the parent spans name is
        Select top 1 D.RSN
        FROM
        spans_with_length D
        WHERE
        D.name =sd.[name]
        AND
        sd.start_dt BETWEEN D.SpStart AND D.SpEnd
        AND
        sd.end_dt BETWEEN D.SpStart AND D.SpEnd
        ORDER BY D.SpanLength
) B
WHERE sd.end_dt IS NOT NULL
ORDER BY sd.[name], sd.start_dt;

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  July 24, 2018 at 11:34 am

  #1998854

  I suggest you read the following article on Packing Intervals.  It outlines a better approach to this.  You really need much more sample data for anyone else to attempt this.  Three rows just doesn't cut it, especially for something as complex as this.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 24, 2018 at 12:05 pm

  #1998860

  drew.allen - Tuesday, July 24, 2018 11:34 AM

  I suggest you read the following article on Packing Intervals.  It outlines a better approach to this.  You really need much more sample data for anyone else to attempt this.  Three rows just doesn't cut it, especially for something as complex as this.

  Drew

  For now we are all set.. I know you don't like me adding requirements.  However  I ran into another 3rd issue. May be if you have a quick suggestion that would be great.
  I was playing with it and I purposely added a new range (  It is labeled as 'NEXT YEARS SELECTION', you will see it in the data ) 
  Everything comes out perfect...except there is a gap between the last span and the one before. It will be ideal if you could tell me how to create a extra row identifying the GAP.  In this case the GAP goes from 20181231.. 20190501

  DO NOT ANSWER IF YOU DONT LIKE...

  IF object_id('tempdb..#SPANS') IS NOT NULL DROP TABLE #SPANS;
CREATE table #SPANS( name VARCHAR(20), SpStart VARCHAR(8), SpEnd VARCHAR(8), RSN VARCHAR(100) );

  --Simplified example by merging original 2 temp-tables into one
INSERT INTO #SPANS
VALUES( 'mez', '20170101', '20181231','PARENT SPAN' ) --a 2 year span
, ( 'mez', '20170601', '20170630','PCP CHANGE' ) --month of June 2017
, ( 'mez', '20180601', '20180630','PCP CHANGE 2' ) --month of June 2018
, ( 'mez', '20180501', '20180615','INSURANCE CHANGE' )
, ( 'mez', '20190501', '20190615','NEXT YEARS SELECTION' )
,

  ( 'baby', '20170101', '20181231','PARENT SPAN' ) --a 2 year span
, ( 'baby', '20170601', '20170630','PCP CHANGE' ) --month of June 2017
, ( 'baby', '20180601', '20180630','PCP CHANGE 2' ) --month of June 2018
, ( 'baby', '20180501', '20180615','INSURANCE CHANGE' )
, ( 'baby', '20190501', '20190615','NEXT YEARS SELECTION' )
;

  WITH
span_dates AS
(
SELECT s.[name], d.dt start_dt
, LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt,DATEDIFF(d, SpStart, SpEnd ) as SpanLength
FROM #SPANS s
CROSS APPLY ( VALUES(s.SpStart, s.RSN), (s.SpEnd, s.RSN) ) d(dt,rsn)
)
,
spans_with_length as
(
  Select
  *, DATEDIFF(d, SpStart, SpEnd ) as SpanLength
  FROM
  #SPANS s
)
,
span_set_with_possible_gaps as
(
SELECT sd.[name], sd.start_dt, sd.end_dt, B.RSN
FROM span_dates sd
CROSS APPLY --Find the parent span that covers the new spans and pick the span reason associated with the parent.
(
   --WHEN a new span is created ( call it a newborn span ), we like to know who the parent spans name is
   Select top 1 D.RSN
   FROM
   spans_with_length D
   WHERE
   D.name =sd.[name]
   AND
   sd.start_dt BETWEEN D.SpStart AND D.SpEnd
   AND
   sd.end_dt BETWEEN D.SpStart AND D.SpEnd
   ORDER BY D.SpanLength
) B
WHERE sd.end_dt IS NOT NULL
)
Select * FROM span_set_with_possible_gaps

• Nate the DBA

  SSC-Addicted

  Points: 430

  More actions

  July 24, 2018 at 12:16 pm

  #1998862

  mw_sql_developer - Tuesday, July 24, 2018 12:05 PM

  I was playing with it and I purposely added a new range (  It is labeled as 'NEXT YEARS SELECTION', you will see it in the data ) 
  Everything comes out perfect...except there is a gap between the last span and the one before. It will be ideal if you could tell me how to create a extra row identifying the GAP.  In this case the GAP goes from 20181231.. 20190501

  My "thought experiment" / pseudo-code (4 steps) would cover this, since it would start with a base table of "all dates" which could then be used to identify dates that don't fall into a given span.  I just didn't bother to spend the time implementing it... that's up to you!  😉

  -Nate the DBA natethedba.com

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 24, 2018 at 4:19 pm

  #1998901

  Man! That was way toooooo easy....  The only thing I had to change was going from  " CROSS APPLY " to "OUTER APPLY"  and that is all I did !
  Didn't realize that the "CROSS APPLY" is more like a INNER JOIN......

  So it works now.......

  If you run the SQL below .... you will see the GAP between 20181231  and   20190501 is detected beautifully.....

  IT WORKS !.....

  
IF object_id('tempdb..#SPANS') IS NOT NULL DROP TABLE #SPANS;
CREATE table #SPANS( name VARCHAR(20), SpStart VARCHAR(8), SpEnd VARCHAR(8), RSN VARCHAR(100) );

  --Simplified example by merging original 2 temp-tables into one
INSERT INTO #SPANS
VALUES( 'mez', '20170101', '20181231','PARENT SPAN' ) --a 2 year span
, ( 'mez', '20170601', '20170630','PCP CHANGE' ) --month of June 2017
, ( 'mez', '20180601', '20180630','PCP CHANGE 2' ) --month of June 2018
, ( 'mez', '20180501', '20180615','INSURANCE CHANGE' )
, ( 'mez', '20190501', '20190615','NEXT YEARS SELECTION' )
,

  ( 'baby', '20170101', '20181231','PARENT SPAN' ) --a 2 year span
, ( 'baby', '20170601', '20170630','PCP CHANGE' ) --month of June 2017
, ( 'baby', '20180601', '20180630','PCP CHANGE 2' ) --month of June 2018
, ( 'baby', '20180501', '20180615','INSURANCE CHANGE' )
, ( 'baby', '20190501', '20190615','NEXT YEARS SELECTION' )
;

  WITH
span_dates AS
(
SELECT s.[name], d.dt start_dt
, LEAD(d.dt) OVER(PARTITION BY s.[name] ORDER BY d.dt) AS end_dt,DATEDIFF(d, SpStart, SpEnd ) as SpanLength
FROM #SPANS s
CROSS APPLY ( VALUES(s.SpStart, s.RSN), (s.SpEnd, s.RSN) ) d(dt,rsn)
)
,
spans_with_length as
(
Select
*, DATEDIFF(d, SpStart, SpEnd ) as SpanLength
FROM
#SPANS s
)
,
span_set_with_any_gaps as
(
SELECT sd.[name], sd.start_dt, sd.end_dt, ISNULL(B.RSN, 'GAP DETECTED') as RSN
FROM span_dates sd
OUTER APPLY --Find the parent span that covers the new spans and pick the span reason associated with the parent.
(
  --WHEN a new span is created ( call it a newborn span ), we like to know who the parent spans name is
  Select top 1 D.RSN
  FROM
  spans_with_length D
  WHERE
  D.name =sd.[name]
  AND
  sd.start_dt BETWEEN D.SpStart AND D.SpEnd
  AND
  sd.end_dt BETWEEN D.SpStart AND D.SpEnd
  ORDER BY D.SpanLength
) B
WHERE sd.end_dt IS NOT NULL
)
Select * FROM span_set_with_any_gaps