September 27, 2018 at 3:05 pm
My earlier post was a challenge. So I made it simple. run the following code.
what i need as output is just 3 lines.
START, ENDDT , SPAN_REASON
20160701, 20180930, Elig_Change
20180407, 20180811, LTI
20180609, 20180708, HOSPICE
Why 3 rows.... I noticed all the Elig_change records had a continuous dates so I made it one span.
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'
September 29, 2018 at 5:28 pm
check out some of the solutions in this post. It's very similar to what you are asking
https://www.sqlservercentral.com/Forums/Topic1971322.aspx
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 30, 2018 at 10:40 am
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';
SELECT table1.emp,
Min(table1.effectivedate) AS startdate,
Max(table2.termdate) AS enddate,
table1.spanreason
FROM (SELECT *,
Row_number()
OVER (
partition BY emp, spanreason
ORDER BY effectivedate) AS rnk
FROM #t)table1
INNER JOIN (SELECT *,
Row_number()
OVER (
partition BY emp, spanreason
ORDER BY effectivedate) AS rnk
FROM #t)table2
ON table1.rnk + 1 = table2.rnk
GROUP BY table1.emp,
table1.spanreason ;
Output:
emp SpanReason startdate enddate
0010970115 Elig_Change 20160701 20180930
0010970115 HOSPICE 20180609 20180708
0010970115 LTI 20180407 20180811
Saravanan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply