Why is SqServer giving me a 1 when it should be a zero .. syntax issue..

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  September 28, 2018 at 2:11 pm

  #415745

  Just run the following code.. There is nothing left for you to do here.
  I am confused... Why am I getting a 1 in the continuous_span when It should be a zero 

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

  Select
'0010970115'    as EMP    
,'20160701'        as EffectiveDate
,'20170131'        as TermDate
,'Elig_Change'    as SpanReason
INTO #t

  INSERT INTO #t( EMP, EffectiveDate, TermDate, SpanReason )
Select '0010970115','20170201'    ,'20171130','Elig_Change'

  INSERT INTO #t( EMP, EffectiveDate, TermDate, SpanReason )
Select '0010970115','20171201',    '20171231','Elig_Change'

  INSERT INTO #t( EMP, EffectiveDate, TermDate, SpanReason )
Select '0010970115','20180101',    '20180930','Elig_Change'

  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
X0 as
(
    select *
    ,ROW_NUMBER() OVER (PARTITION BY EMP ORDER BY EFFECTIVEDATE ) as RN
    ,
    CONVERT( CHAR(8),
    DATEADD(D,1,
    LAG(TermDate) OVER(PARTITION BY EMP ORDER BY EffectiveDate))
    ,112) AS PreviousTermDatePlusOne
    FROM
    #t
)
,
AB as
(
    Select
    CASE WHEN PreviousTermDatePlusOne <> EffectiveDate THEN 0 ELSE 1 END as CONTINUOUS_SPAN
    ,
    *
    FROM
    X0
)
Select * FROM AB ORDER BY 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 28, 2018 at 3:40 pm

  #2007414

  mw_sql_developer - Friday, September 28, 2018 2:11 PM

  Just run the following code.. There is nothing left for you to do here.
  I am confused... Why am I getting a 1 in the continuous_span when It should be a zero 

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

  Select
'0010970115'    as EMP    
,'20160701'        as EffectiveDate
,'20170131'        as TermDate
,'Elig_Change'    as SpanReason
INTO #t

  INSERT INTO #t( EMP, EffectiveDate, TermDate, SpanReason )
Select '0010970115','20170201'    ,'20171130','Elig_Change'

  INSERT INTO #t( EMP, EffectiveDate, TermDate, SpanReason )
Select '0010970115','20171201',    '20171231','Elig_Change'

  INSERT INTO #t( EMP, EffectiveDate, TermDate, SpanReason )
Select '0010970115','20180101',    '20180930','Elig_Change'

  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
X0 as
(
    select *
    ,ROW_NUMBER() OVER (PARTITION BY EMP ORDER BY EFFECTIVEDATE ) as RN
    ,
    CONVERT( CHAR(8),
    DATEADD(D,1,
    LAG(TermDate) OVER(PARTITION BY EMP ORDER BY EffectiveDate))
    ,112) AS PreviousTermDatePlusOne
    FROM
    #t
)
,
AB as
(
    Select
    CASE WHEN PreviousTermDatePlusOne <> EffectiveDate THEN 0 ELSE 1 END as CONTINUOUS_SPAN
    ,
    *
    FROM
    X0
)
Select * FROM AB ORDER BY 2

  Which result row are you talking about?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 28, 2018 at 3:47 pm

  #2007415

  If it is the first row where PreviousTermDatePlusOne is NULL, that is reason; 20160701 <> NULL is not true therefore the result returned is 1.  Actually that statement isn't false either it is actually UNKNOWN.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 1, 2018 at 4:11 pm

  #2007619

  Just checking to see if you got the answer you were looking for her.