How do I break down into continuous spans ( I did the coding ) - You can make the corrections please

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 1, 2018 at 11:00 am

  #363590

  The code below runs, but I have posted the desired output ( immediately below )
  Hmm.. My coding ( Look in the C2 section of the WITH block ) is wrong.
  Anyone who can tweak it please ...

  
EMP, EffectiveDate, TermDate, Reason
'0010970115',20150201,20180407, Elig_Change
'0010970115',20180407,20180608, LTI
'0010970115',20180608,20180708, HOSPICE
'0010970115',20180708,20180811, LTI
'0010970115',20180811,20180930, Elig_Change


  IF Object_id('tempdb..#t') IS NOT NULL
DROP TABLE #t;

  SELECT '0010970115' AS EMP,
   '20150201'  AS EffectiveDate,
   '20180930'  AS TermDate,
   'Elig_Change' AS SpanReason
INTO #t

  INSERT INTO #t
    (emp,
     effectivedate,
     termdate,
     spanreason)
SELECT '0010970115',
   '20180407',
   '20180811',
   'LTI'

  INSERT INTO #t
    (emp,
     effectivedate,
     termdate,
     spanreason)
SELECT '0010970115',
   '20180609',
   '20180708',
   'HOSPICE'

  ;
With C1 as
(
    Select
    ROW_NUMBER() OVER (PARTITION BY EMP, SpanReason, EffectiveDate ORDER BY EventStartOrEndDate ) as RN
    ,*
    FROM
    #t
    CROSS APPLY
    (
        VALUES (EffectiveDate) , (TermDate)
    ) d(EventStartOrEndDate)

  )
,
C2 as
(
    Select EMP, EventStartOrEndDate as EffectiveDate,
    LEAD(EventStartOrEndDate) OVER(PARTITION BY EMP ORDER BY EventStartOrEndDate) AS TermDate,
    SpanReason,EventStartOrEndDate
    FROM
    C1
)
Select * FROM C2 ORDER BY EMP,EventStartOrEndDate ;

  /*
EMP, EffectiveDate, TermDate, Reason
'0010970115',20150201,20180407, Elig_Change
'0010970115',20180407,20180608, LTI
'0010970115',20180608,20180708, HOSPICE
'0010970115',20180708,20180811, LTI
'0010970115',20180811,20180930, Elig_Change
*/

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 1, 2018 at 11:18 am

  #2007554

  mw_sql_developer - Monday, October 1, 2018 11:00 AM

  The code below runs, but I have posted the desired output ( immediately below )
  Hmm.. My coding ( Look in the C2 section of the WITH block ) is wrong.
  Anyone who can tweak it please ...

  
EMP, EffectiveDate, TermDate, Reason
'0010970115',20150201,20180407, Elig_Change
'0010970115',20180407,20180608, LTI
'0010970115',20180608,20180708, HOSPICE
'0010970115',20180708,20180811, LTI
'0010970115',20180811,20180930, Elig_Change


  IF Object_id('tempdb..#t') IS NOT NULL
DROP TABLE #t;

  SELECT '0010970115' AS EMP,
   '20150201'  AS EffectiveDate,
   '20180930'  AS TermDate,
   'Elig_Change' AS SpanReason
INTO #t

  INSERT INTO #t
    (emp,
     effectivedate,
     termdate,
     spanreason)
SELECT '0010970115',
   '20180407',
   '20180811',
   'LTI'

  INSERT INTO #t
    (emp,
     effectivedate,
     termdate,
     spanreason)
SELECT '0010970115',
   '20180609',
   '20180708',
   'HOSPICE'

  ;
With C1 as
(
    Select
    ROW_NUMBER() OVER (PARTITION BY EMP, SpanReason, EffectiveDate ORDER BY EventStartOrEndDate ) as RN
    ,*
    FROM
    #t
    CROSS APPLY
    (
        VALUES (EffectiveDate) , (TermDate)
    ) d(EventStartOrEndDate)

  )
,
C2 as
(
    Select EMP, EventStartOrEndDate as EffectiveDate,
    LEAD(EventStartOrEndDate) OVER(PARTITION BY EMP ORDER BY EventStartOrEndDate) AS TermDate,
    SpanReason,EventStartOrEndDate
    FROM
    C1
)
Select * FROM C2 ORDER BY EMP,EventStartOrEndDate ;

  /*
EMP, EffectiveDate, TermDate, Reason
'0010970115',20150201,20180407, Elig_Change
'0010970115',20180407,20180608, LTI
'0010970115',20180608,20180708, HOSPICE
'0010970115',20180708,20180811, LTI
'0010970115',20180811,20180930, Elig_Change
*/

  Ahh..  That was not too bad! I found the solution.. All set ! Cool

  IF Object_id('tempdb..#t') IS NOT NULL
DROP TABLE #t;

  SELECT '0010970115' AS EMP,
   '20150201'  AS EffectiveDate,
   '20180930'  AS TermDate,
   'Elig_Change' AS SpanReason
INTO #t

  INSERT INTO #t
    (emp,
     effectivedate,
     termdate,
     spanreason)
SELECT '0010970115',
   '20180407',
   '20180811',
   'LTI'

  INSERT INTO #t
    (emp,
     effectivedate,
     termdate,
     spanreason)
SELECT '0010970115',
   '20180609',
   '20180708',
   'HOSPICE'

  ;
With C1 as
(
    Select
    ROW_NUMBER() OVER (PARTITION BY EMP, SpanReason, EffectiveDate ORDER BY EventStartOrEndDate ) as RN
    ,*
    FROM
    #t
    CROSS APPLY
    (
        VALUES (EffectiveDate) , (TermDate)
    ) d(EventStartOrEndDate)

  )
,
C2 as
(
    Select EMP, EventStartOrEndDate as EffectiveDate,

      LEAD(EventStartOrEndDate) OVER(PARTITION BY EMP ORDER BY EventStartOrEndDate) AS TermDate,
    CASE WHEN RN=1 THEN
    SpanReason
    ELSE
    LEAD(SpanReason) OVER(PARTITION BY EMP ORDER BY EventStartOrEndDate)
    END as SpanReason
    ,
    EventStartOrEndDate
    FROM
    C1
)
Select * FROM C2 WHERE SpanReason IS NOT NULL ORDER BY EMP,EventStartOrEndDate ;

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 1, 2018 at 11:20 am

  #2007556

  
  Thank you Folks! No further help needed ....  This solution works.. Run and see 

  
IF Object_id('tempdb..#t') IS NOT NULL
DROP TABLE #t;

  SELECT '0010970115' AS EMP,
   '20150201'  AS EffectiveDate,
   '20180930'  AS TermDate,
   'Elig_Change' AS SpanReason
INTO #t

  INSERT INTO #t
    (emp,
     effectivedate,
     termdate,
     spanreason)
SELECT '0010970115',
   '20180407',
   '20180811',
   'LTI'

  INSERT INTO #t
    (emp,
     effectivedate,
     termdate,
     spanreason)
SELECT '0010970115',
   '20180609',
   '20180708',
   'HOSPICE'

  ;
With C1 as
(
    Select
    ROW_NUMBER() OVER (PARTITION BY EMP, SpanReason, EffectiveDate ORDER BY EventStartOrEndDate ) as RN
    ,*
    FROM
    #t
    CROSS APPLY
    (
        VALUES (EffectiveDate) , (TermDate)
    ) d(EventStartOrEndDate)

  )
,
C2 as
(
    Select EMP, EventStartOrEndDate as EffectiveDate,

      LEAD(EventStartOrEndDate) OVER(PARTITION BY EMP ORDER BY EventStartOrEndDate) AS TermDate,
    CASE WHEN RN=1 THEN
    SpanReason
    ELSE
    LEAD(SpanReason) OVER(PARTITION BY EMP ORDER BY EventStartOrEndDate)
    END as SpanReason,
    EventStartOrEndDate
    FROM
    C1
)
Select * FROM C2 WHERE SpanReason IS NOT NULL ORDER BY EMP,EventStartOrEndDate ;