September 27, 2018 at 10:42 am
I have listed the desired output within the code. So what we are trying to do is to get to merge spans and also come out with a distinct set of spans that represent at each time interval to what span the member belonged to.
So either the MEMBER was in LTI or HOSPICE or simply he was normally enrolled ( I called it ELIG_CHANGE )
see if you can help me get the SQL for getting the desired output
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','20170201' ,'20171130','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'
Select * , ROW_NUMBER() OVER (PARTITION BY EMP ORDER BY EventStartOrEndDate ) as RN
FROM
(
Select EMP, SpanReason, EventStartOrEndDate
FROM
#t
CROSS APPLY
(
VALUES (EffectiveDate) , (TermDate)
) d(EventStartOrEndDate)
)A
--Help me get to the output below..Doesnt have to be all the fields, at least the dates along with EMP number would be fine
/*
EMP EffectiveDate TermDate LTI Hospice , SpanReason
0010970115 20160701 20180406 Elig_Change
0010970115 20180407 20180608 Y LTI
0010970115 20180609 20180708 Y Y HOSPICE
0010970115 20180709 20180811 Y LTI
0010970115 20180812 20180930 Elig_Change
*/
September 27, 2018 at 6:29 pm
WITH x
AS
(Select A.EMP
, A.SpanReason
, A.EventStartOrEndDate
, ROW_NUMBER() OVER (PARTITION BY A.EMP ORDER BY A.EventStartOrEndDate ) as RN
FROM
(
Select t.EMP
, t.SpanReason
, b.EventStartOrEndDate
FROM
#t t
CROSS APPLY
(
SELECT d.EventStartOrEndDate
FROM (
VALUES (EffectiveDate)
, (TermDate)
) d (EventStartOrEndDate)
) b
) A )
SELECT x.EMP
, Min(x.EventStartOrEndDate) EffectiveDate
, Max(x.EventStartOrEndDate) TermDate
, Max(CASE WHEN x.SpanReason = 'LTI' THEN 'Y' ELSE NULL END) LTI
, Max(CASE WHEN x.SpanReason = 'Hospice' THEN 'Y' ELSE NULL END) Hospice
, Max(CASE WHEN x.SpanReason NOT IN( 'LTI', 'Hospice') THEN 'Y' ELSE NULL END) Elig_Change
, x.SpanReason
FROM x
GROUP BY x.EMP, x.SpanReason;
September 28, 2018 at 2:02 pm
Joe Torre - Thursday, September 27, 2018 6:29 PMWITH x
AS
(Select A.EMP
, A.SpanReason
, A.EventStartOrEndDate
, ROW_NUMBER() OVER (PARTITION BY A.EMP ORDER BY A.EventStartOrEndDate ) as RN
FROM
(
Select t.EMP
, t.SpanReason
, b.EventStartOrEndDate
FROM
#t t
CROSS APPLY
(
SELECT d.EventStartOrEndDate
FROM (
VALUES (EffectiveDate)
, (TermDate)
) d (EventStartOrEndDate)
) b
) A )
SELECT x.EMP
, Min(x.EventStartOrEndDate) EffectiveDate
, Max(x.EventStartOrEndDate) TermDate
, Max(CASE WHEN x.SpanReason = 'LTI' THEN 'Y' ELSE NULL END) LTI
, Max(CASE WHEN x.SpanReason = 'Hospice' THEN 'Y' ELSE NULL END) Hospice
, Max(CASE WHEN x.SpanReason NOT IN( 'LTI', 'Hospice') THEN 'Y' ELSE NULL END) Elig_Change
, x.SpanReason
FROM x
GROUP BY x.EMP, x.SpanReason;
Good Job! Yey !
September 28, 2018 at 2:28 pm
mw_sql_developer - Friday, September 28, 2018 2:02 PMJoe Torre - Thursday, September 27, 2018 6:29 PMWITH x
AS
(Select A.EMP
, A.SpanReason
, A.EventStartOrEndDate
, ROW_NUMBER() OVER (PARTITION BY A.EMP ORDER BY A.EventStartOrEndDate ) as RN
FROM
(
Select t.EMP
, t.SpanReason
, b.EventStartOrEndDate
FROM
#t t
CROSS APPLY
(
SELECT d.EventStartOrEndDate
FROM (
VALUES (EffectiveDate)
, (TermDate)
) d (EventStartOrEndDate)
) b
) A )
SELECT x.EMP
, Min(x.EventStartOrEndDate) EffectiveDate
, Max(x.EventStartOrEndDate) TermDate
, Max(CASE WHEN x.SpanReason = 'LTI' THEN 'Y' ELSE NULL END) LTI
, Max(CASE WHEN x.SpanReason = 'Hospice' THEN 'Y' ELSE NULL END) Hospice
, Max(CASE WHEN x.SpanReason NOT IN( 'LTI', 'Hospice') THEN 'Y' ELSE NULL END) Elig_Change
, x.SpanReason
FROM x
GROUP BY x.EMP, x.SpanReason;Good Job! Yey !
Good Job Joe!
i was alo playing with it heavily and came up with the answer below.. It works !
My one does some extra checking... That is it will check whether those spans are continuous without gaps in between in dates..
Anyhow, I had not asked this in the original question but later came to my mind...
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','20150201' ,'20151130','Elig_Change'
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
)
,
X1 as
(
Select
CASE WHEN ( PreviousTermDatePlusOne != EffectiveDate) THEN 0 ELSE 1 END as CONTINUOUS_SPAN
,
*
FROM
X0
)
,
X2 as
(
Select * ,
DENSE_RANK() OVER (PARTITION BY EMP, Spanreason,CONTINUOUS_SPAN ORDER BY EffectiveDate )-RN as GRP
FROM
X1
)
,
X3 as
(
Select EMP, GRP, MIN(SpanReason) as SpanReason, MIN(EffectiveDate) StartDt , MAX(TermDate) EndDt
FROM
X2 GROUP BY EMP, GRP
)
Select * FROM X3
ORDER BY 4
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply