Split records, separate the overlapped dates


  
 DECLARE @TBL_TEMP TABLE(MBR_NBR VARCHAR(100),PLAN_ID VARCHAR(100),MBR_ELIG_EFFDTE DATE,MBR_ELIG_TRMDTE DATE)

INSERT @TBL_TEMP

SELECT '101162464','MCR000','2016-01-01','2017-12-31' UNION
SELECT '101162464','MCRMCD08','2016-08-01','2017-04-30' UNION

SELECT '14601938','MCRADV02','2016-01-01','2016-04-30' UNION
SELECT '14601938','MCRMCD02','2016-05-01','2017-04-30' UNION
SELECT '14601938','MCR000','2017-05-01','2017-12-31' 

 

;with mycte as (
SELECT MBR_NBR,
Case when n=1 then lead(PLAN_ID) Over(Partition by MBR_NBR  Order by dt ) else PLAN_ID end PLAN_ID,
dateadd(day,n,dt) MBR_ELIG_EFFDTE, n,n2,
dateadd(day,-lead(n2) Over(Partition  by MBR_NBR Order by dt ),lead(dt) Over(Partition by MBR_NBR Order by dt ))  MBR_ELIG_TRMDTE

 FROM @TBL_TEMP
cross apply (values(0,1,MBR_ELIG_EFFDTE),(1,0,MBR_ELIG_TRMDTE)  ) d(n,n2,dt)
)

Select  MBR_NBR,PLAN_ID, MBR_ELIG_EFFDTE,MBR_ELIG_TRMDTE 
--,Row_number() Over(Partition by MBR_NBR,MBR_ELIG_EFFDTE Order by MBR_ELIG_TRMDTE desc)  rn
--,n,n2
 from mycte 
 WHERE MBR_ELIG_TRMDTE is not null  and MBR_ELIG_EFFDTE < MBR_ELIG_TRMDTE
order by   MBR_NBR, MBR_ELIG_EFFDTE



-- expected resultset should like below
DECLARE @TBL_TEMP2 TABLE(MBR_NBR VARCHAR(100),PLAN_ID VARCHAR(100),MBR_ELIG_EFFDTE DATE,MBR_ELIG_TRMDTE DATE)

INSERT @TBL_TEMP2

SELECT '101162464','MCR000','2016-01-01','2016-07-31' UNION
SELECT '101162464','MCRMCD08','2016-08-01','2017-04-30' UNION
SELECT '101162464','MCR000','2017-05-01','2017-12-31' UNION

SELECT '14601938','MCRADV02','2016-01-01','2016-04-30' UNION
SELECT '14601938','MCRMCD02','2016-05-01','2017-04-30' UNION
SELECT '14601938','MCR000','2017-05-01','2017-12-31' 


SELECT * FROM @TBL_TEMP2 ORDER BY 1,3


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4897356c-a293-414e-9af6-672d7ab7e078/split-records-separate-the-overlapped-dates?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s