Pulling only members who have no gaps between dates and <= 45 days non continuos enrollment

jtammyg1 · Jun 18, 2014

Hi!

Thank you in advanced for helping me.

I have been searching and tried a few examples but none gave me what I am looking for.

I have the following scenario:
Memberid | enrollment date | termination date

1111 | 9/1/2009 | 9/7/2009
1111 | 9/8/2009 | 9/30/2009
2222 | 9/1/2009 | 6/30/2010
2222 | 7/1/2010 | 4/30/2013
2222 | 8/1/2013 | 12/31/2013
2222 | 2/1/2013 | 12/31/2199
3333 | 11/1/2009 | 11/3/2009
3333 | 11/4/2009 | 6/30/2010
3333 | 7/1/2010 | 12/31/2199
4444 | 1/1/2013 | 12/31/2013
5555 | 11/1/2009 | 11/1/2009
6666 | 11/1/2009 | 11/3/2009
6666 |11/4/2009 | 3/31/2010
6666 | 4/1/2010 | 6/30/2010
6666 | 7/1/2010 | 8/31/2010
6666 | 9/1/2010 | 11/30/2010
6666 | 12/1/2010 | 4/30/2013
6666 | 5/1/2013 | 8/31/2013

I need to get only those members with no more than one gap in enrollment of up to 45 days during 1/1/2013 through 12/31/2013 inclusive or where the termination date is > than 12/31/2013 but with no more than 45 days gap.

So considering the above the member 1111 should be discarded since his enrollment is not during 2013.

Member 2222 would also be discarded since there is more than 45 days from termination date = 4/30/2013 and the next enrollment date being 8/1/2013.

Member 3333 should be included since only for the enrollment date = 7/1/2010 and termination date = 12/31/2199.

Member 4444 should be included since it's from 1/1/2013 through 12/31/2013.

Member 5555 should be discarded since it is not through 2013.

Member 6666 should not be included since the last termination date is 8/31/2013.

I hope I explained this clearly.

I'm looking for a solution that runs relatively fast and is easy to understand to run in SQL Server 2005 or 2008. Please explain the steps if you can.

I have already tried the following but it is not capturing all the necessry members:

Select distinct Memberid,MIN(enrollmentdate),MAX(terminationdate) from NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT
where DATEDIFF(DAY,enrollmentdate,terminationdate) <= 45
group by Memberid
HAVING DATEPART(YEAR,MAX(terminationdate)) = '2013'

Thank you so much in advanced!

TG

gk53 · Jun 18, 2014

it looks like something not right with your data sample
3 2222 2013-02-01 2199-12-31
4 2222 2013-08-01 2013-12-31
member 22 has 2 entries which overlaps... it is wrong data or 222 should be included...

jtammyg1 · Jun 18, 2014

@gk53, you are correct, actually row 4 of member 2222 shouldn't be there. I will edit and remove it.
Thanks for catching that.

TamarGranor · Jun 18, 2014

Just fiddling with some ideas here. First, let's get rid of all records that don't include 2013 at all.

Code:

SELECT * 
  FROM Enrollment 
  WHERE EnrollDate > '12/31/13' 
     OR TermDate < '1/1/2013'

Next, from that group, we want to eliminate anyone whose highest termination date is less than 12/31/13:

Code:

WITH csr2013Only MemberID, EnrollDate, TermDate) AS
(SELECT MemberID, EnrollDate, TermDate
  FROM Enrollment 
  WHERE EnrollDate > '12/31/2013' 
     OR TermDate < '1/1/2013'), 
csrAtYearEnd (MemberID) AS
(SELECT MemberID
  FROM csr2013Only 
  WHERE TermDate >= '12/31/2013')

SELECT MemberID, EnrollDate, TermDate
  FROM csr2013Only 
    JOIN csrAtYearEnd 
      ON csr2013Only.MemberID = csrAtYearEnd.MemberID

At this point (if this untested code is right), you have a cursor that contains all records that include 2013, but only for those folks who were enrolled at year end. Of course, this still leaves the hardest part--taking all the records for one person and matching them up end-to-end to look for gaps. I wonder if you could do something with a recursive CTE for that. I'll leave it perking in the back of my head.

Tamar

jtammyg1 · Jun 18, 2014

Hi Tamar!

I wish it was as easy as remove all the outliers that do not belong to 2013. Unfortunately, with the data I have I cannot do that.
e.g 3333 | 7/1/2010 | 12/31/2199

This member should be included since it has been enrolled since 7/1/2010 and it's still current throughout 2013, because his termination date is 12/31/2199 (it's a placeholder to indicate it's still enrolled).

Thank you for your help and trying to come up with ideas.

I have also used the following until now, but I'm stuck as to how to check for the gaps when there is more than one record per memberid for the 45 days we allowed them to be disenrolled.
I also am checking for age since I need anybody age >= 2 years old (this part is not an issue though)

/****** Pull only the members that have Dental Benefit for NJ Medicaid ******/
SELECT DEV.ProductID
,DEV.MemberID
,DEV.UniversalMemberID
,EnrollmentDate
,TerminationDate
,DMV.DOB
,ProviderID
,GroupID
,DentalBenefit
,Region
,SubscriberID
,ProviderOrganizationId
INTO ##NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT
FROM EnrollmentView DEV
JOIN MemberView DMV
ON DEV.MemberID = DMV.MemberID
WHERE DentalBenefit = 'Y' AND DEV.ProductID = '07'

/*Pull members with enrollment date less or equal than 01/01/2013 and termination date higher or equal than 2013*/
SELECT ProductID
,MemberID
,UniversalMemberID
,EnrollmentDate
,TerminationDate
,DOB
,ProviderID
,GroupID
,DentalBenefit
,Region
,SubscriberID
,ProviderOrganizationId
INTO NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT_Enrless_112013
FROM NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT
where (CONVERT(nvarchar, EnrollmentDate, 112) <= '20130101'
AND YEAR(TerminationDate) >= '2013')
ORDER BY UniversalMemberID,EnrollmentDate,TerminationDate

/*Pull members with enrollment date between 1/1/2013 and 12/31/2013*/
select *
into NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT_Enrbtw_112013_12312013
from NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT
where CONVERT(nvarchar, EnrollmentDate, 112) between '20130101' and '20131231'

/*insert results of 2 queries immediately above into a staging table*/
SELECT ProductID
,MemberID
,UniversalMemberID
,EnrollmentDate
,TerminationDate
,datediff(dd,enrollmentdate,terminationdate) as DateDif_Enr_Term
,DOB
,floor(datediff(day, [DOB], CONVERT(nvarchar, '20131231', 112))/(365.25)) as Age
,ProviderID
,GroupID
,DentalBenefit
,Region
,SubscriberID
,ProviderOrganizationId
into NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT_COMBO
FROM NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT_Enrless_112013
order by Memberid,Enrollmentdate,TerminationDate

/*insert results of 2 queries immediately above into a staging table*/
insert into NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT_COMBO
SELECT ProductID
,MemberID
,UniversalMemberID
,EnrollmentDate
,TerminationDate
,datediff(dd,enrollmentdate,terminationdate) as DateDif_Enr_Term
,DOB
,floor(datediff(day, [DOB], CONVERT(nvarchar, '20131231', 112))/(365.25)) as Age
,ProviderID
,GroupID
,DentalBenefit
,Region
,SubscriberID
,ProviderOrganizationId
FROM NJ_ADV_MEDICAID_WITH_DENTAL_BENEFIT_Enrbtw_112013_12312013
order by Memberid,Enrollmentdate,TerminationDate

gk53 · Jun 19, 2014

try that

SQL:

declare @tbl as table
(
Memberid int,
enrollment date,
termination date

)
insert into @tbl
select 1111, '9/1/2009', '9/7/2009'
insert into @tbl
select 1111 , '9/8/2009', '9/30/2009'
insert into @tbl
select 2222 , '9/1/2009', '6/30/2010'
insert into @tbl
select 2222 , '7/1/2010', '4/30/2013'
insert into @tbl
select 2222 , '8/1/2013', '12/31/2013'
insert into @tbl
select 3333 , '11/1/2009', '11/3/2009'
insert into @tbl
select 3333 , '11/4/2009', '6/30/2010'
insert into @tbl
select 3333 , '7/1/2010', '12/31/2199'
insert into @tbl
select 4444 , '1/1/2013', '12/31/2013'
insert into @tbl
select 5555 , '11/1/2009', '11/1/2009'
insert into @tbl
select 6666 , '11/1/2009', '11/3/2009'
insert into @tbl
select 6666 , '11/4/2009', '3/31/2010'
insert into @tbl
select 6666 , '4/1/2010', '6/30/2010'
insert into @tbl
select 6666 , '7/1/2010', '8/31/2010'
insert into @tbl
select 6666 , '9/1/2010', '11/30/2010'
insert into @tbl
select 6666 , '12/1/2010', '4/30/2013'
insert into @tbl
select 6666 , '5/1/2013', '8/31/2013'


;with cte as (
select activeMembers = Memberid,
			enrollments = COUNT(*)
	from @tbl
	where termination >= '12/31/2013'
	group by Memberid	
)
	
select distinct t1.Memberid 
from (
	select  row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment),
			Memberid,
			enrollment,
			termination,
			c.enrollments
	from @tbl  t
	join cte c
	on t.Memberid = c.activeMembers
	) t1
	join (
			select row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment),
					Memberid,
					enrollment,
					termination
					
			from @tbl  t
			join cte c
			on t.Memberid = c.activeMembers
			) t2
	on t1.Memberid = t2.Memberid
	and t1.row + 1 = t2.row
	where DATEDIFF(day, t1.termination, t2.enrollment) < 45
	order by t1.Memberid

jtammyg1 · Jun 19, 2014

Hi gk53!

Thank you for that, but there is still something not quite right.

The result I get back is:

Memberid
2222
3333

The member 2222 shouldn't be included since he/she has more than a 45 days between the terminationdate '4/30/2013' and the next enrollmentdate of '8/1/2013'.

2222 , '9/1/2009', '6/30/2010'
2222 , '7/1/2010', '4/30/2013'
2222 , '8/1/2013', '12/31/2013'

Whereas the member 4444 should be included since he only has one row and is a straightforward enrollment and termination date of '1/1/2013' and '12/31/2014'.

Your help is greatly appreciated, gk53!!!!!

Tammy

gk53 · Jun 19, 2014

it looks like this one give you what you need

SQL:

declare @tbl as table
(
Memberid int,
enrollment date,
termination date

)
insert into @tbl
select 1111, '9/1/2009', '9/7/2009'
insert into @tbl
select 1111 , '9/8/2009', '9/30/2009'
insert into @tbl
select 2222 , '9/1/2009', '6/30/2010'
insert into @tbl
select 2222 , '7/1/2010', '4/30/2013'
insert into @tbl
select 2222 , '8/1/2013', '12/31/2013'
insert into @tbl
select 3333 , '11/1/2009', '11/3/2009'
insert into @tbl
select 3333 , '11/4/2009', '6/30/2010'
insert into @tbl
select 3333 , '7/1/2010', '12/31/2199'
insert into @tbl
select 4444 , '1/1/2013', '12/31/2013'
insert into @tbl
select 5555 , '11/1/2009', '11/1/2009'
insert into @tbl
select 6666 , '11/1/2009', '11/3/2009'
insert into @tbl
select 6666 , '11/4/2009', '3/31/2010'
insert into @tbl
select 6666 , '4/1/2010', '6/30/2010'
insert into @tbl
select 6666 , '7/1/2010', '8/31/2010'
insert into @tbl
select 6666 , '9/1/2010', '11/30/2010'
insert into @tbl
select 6666 , '12/1/2010', '4/30/2013'
insert into @tbl
select 6666 , '5/1/2013', '8/31/2013'



;with cte as (
select activeMembers = Memberid,
			enrollments = COUNT(*)
	from @tbl
	where termination >= '12/31/2013'
	group by Memberid	
)
select distinct inclide.Memberid
	from 
	(select t1.*, gap = DATEDIFF(day, t1.termination, isnull(t2.enrollment,'12/31/3999')), nextEnrollment = isnull(t2.enrollment,'12/31/3999')
	from (
		select  row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
				Memberid,
				enrollment,
				termination,
				c.enrollments
		from @tbl  t
		join cte c
		on t.Memberid = c.activeMembers
		) t1
		left join (
				select row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
						Memberid,
						enrollment,
						termination
				from @tbl  t
				join cte c
				on t.Memberid = c.activeMembers
				) t2
		on t1.Memberid = t2.Memberid
		and t1.row + 1 = t2.row 
		) as inclide
	left join 
		(select t1.*, gap = DATEDIFF(day, t1.termination, isnull(t2.enrollment,'12/31/3999')), nextEnrollment = isnull(t2.enrollment,'12/31/3999')
		from (
			select  row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
					Memberid,
					enrollment,
					termination,
					c.enrollments
			from @tbl  t
			join cte c
			on t.Memberid = c.activeMembers
			) t1
			left join (
					select row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
							Memberid,
							enrollment,
							termination
					from @tbl  t
					join cte c
					on t.Memberid = c.activeMembers
					) t2
			on t1.Memberid = t2.Memberid
			and t1.row + 1 = t2.row 
			where   DATEDIFF(day, t1.termination, isnull(t2.enrollment,t1.termination)) > 45
			and (t1.termination between '1/1/2013' and '12/31/2013' 
			or isnull(t2.enrollment,t1.termination) between '1/1/2013' and '12/31/2013' )
			) as exclude
	on inclide.Memberid = exclude.Memberid
	where exclude.Memberid is null

jtammyg1 · Jun 19, 2014

Hi gk53!

I'm currently testing it with the actual data to see if it gives me all I need. With the test data it works great.

Can you please explain what each SELECT part is doing? I'm confused :-(

Thank you so much for your help [bigsmile]

!!!!

Tammy

gk53 · Jun 19, 2014

select activeMembers = Memberid,
enrollments = COUNT(*)
from @tbl
where termination >= '12/31/2013'
group by Memberid

just get memberId's for members which was is current, remove records if member was exist in the past and not exist after '12/31/2013'
you need to link firs record for member with next record, so you need to add row Number in my case row column for activeMembers from first statement
select row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
Memberid,
enrollment,
termination,
c.enrollments
from @tbl t
join cte c
on t.Memberid = c.activeMembers

and left join with the same selection on on t1.Memberid = t2.Memberid
and t1.row + 1 = t2.row

as a results you have
row Memberid enrollment termination enrollments gap nextEnrollment
1 2222 2009-09-01 2010-06-30 1 1 2010-07-01
2 2222 2010-07-01 2013-04-30 1 93 2013-08-01
3 2222 2013-08-01 2013-12-31 1 725371 3999-12-31
1 3333 2009-11-01 2009-11-03 1 1 2009-11-04
2 3333 2009-11-04 2010-06-30 1 1 2010-07-01
3 3333 2010-07-01 2199-12-31 1 657436 3999-12-31
1 4444 2013-01-01 2013-12-31 1 725371 3999-12-31
all entries to check (include)
now you need exclude members which has gap greater then 45 days in period between '1/1/2013' and '12/31/2013'
the sane sql only with where condition (exclude)
select t1.*, gap = DATEDIFF(day, t1.termination, isnull(t2.enrollment,'12/31/3999')), nextEnrollment = isnull(t2.enrollment,'12/31/3999')
from (
select row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
Memberid,
enrollment,
termination,
c.enrollments
from @tbl t
join cte c
on t.Memberid = c.activeMembers
) t1
left join (
select row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
Memberid,
enrollment,
termination
from @tbl t
join cte c
on t.Memberid = c.activeMembers
) t2
on t1.Memberid = t2.Memberid
and t1.row + 1 = t2.row
where DATEDIFF(day, t1.termination, isnull(t2.enrollment,t1.termination)) > 45
and (t1.termination between '1/1/2013' and '12/31/2013'
or isnull(t2.enrollment,t1.termination) between '1/1/2013' and '12/31/2013'
and last step is
select distinct memberid
from include
left join exclude
on include.memberid = exclude.memberid
where exclude.memberid is null
which is equivalent to
select * from include where memberid not in (select distinct memberId from exclude)

jtammyg1 · Jun 20, 2014

Hi gk53!

Great explanation!!!!

Now if I were to load this onto a temp table, where in your query do I put it?

Again this is great thank you so much for your time and help!!! [glasses]

Tammy

gk53 · Jun 20, 2014

if you want to use temp table instead of table variable change

SQL:

declare @tbl as table
(
Memberid int,
enrollment date,
termination date

)
insert into @tbl
select 1111, '9/1/2009', '9/7/2009'
insert into @tbl
select 1111 , '9/8/2009', '9/30/2009'
insert into @tbl
select 2222 , '9/1/2009', '6/30/2010'
insert into @tbl
select 2222 , '7/1/2010', '4/30/2013'
insert into @tbl
select 2222 , '8/1/2013', '12/31/2013'
insert into @tbl
select 3333 , '11/1/2009', '11/3/2009'
insert into @tbl
select 3333 , '11/4/2009', '6/30/2010'
insert into @tbl
select 3333 , '7/1/2010', '12/31/2199'
insert into @tbl
select 4444 , '1/1/2013', '12/31/2013'
insert into @tbl
select 5555 , '11/1/2009', '11/1/2009'
insert into @tbl
select 6666 , '11/1/2009', '11/3/2009'
insert into @tbl
select 6666 , '11/4/2009', '3/31/2010'
insert into @tbl
select 6666 , '4/1/2010', '6/30/2010'
insert into @tbl
select 6666 , '7/1/2010', '8/31/2010'
insert into @tbl
select 6666 , '9/1/2010', '11/30/2010'
insert into @tbl
select 6666 , '12/1/2010', '4/30/2013'
insert into @tbl
select 6666 , '5/1/2013', '8/31/2013'

to

SQL:

create table #tbl
(
Memberid int,
enrollment date,
termination date

)
insert into #tbl
select 1111, '9/1/2009', '9/7/2009'
insert into #tbl
select 1111 , '9/8/2009', '9/30/2009'
insert into #tbl
select 2222 , '9/1/2009', '6/30/2010'
insert into #tbl
select 2222 , '7/1/2010', '4/30/2013'
insert into #tbl
select 2222 , '8/1/2013', '12/31/2013'
insert into #tbl
select 3333 , '11/1/2009', '11/3/2009'
insert into #tbl
select 3333 , '11/4/2009', '6/30/2010'
insert into #tbl
select 3333 , '7/1/2010', '12/31/2199'
insert into #tbl
select 4444 , '1/1/2013', '12/31/2013'
insert into #tbl
select 5555 , '11/1/2009', '11/1/2009'
insert into #tbl
select 6666 , '11/1/2009', '11/3/2009'
insert into #tbl
select 6666 , '11/4/2009', '3/31/2010'
insert into #tbl
select 6666 , '4/1/2010', '6/30/2010'
insert into #tbl
select 6666 , '7/1/2010', '8/31/2010'
insert into #tbl
select 6666 , '9/1/2010', '11/30/2010'
insert into #tbl
select 6666 , '12/1/2010', '4/30/2013'
insert into #tbl
select 6666 , '5/1/2013', '8/31/2013'

and any reference on select statement from @tbl to #tbl

jtammyg1 · Jun 20, 2014

hi again gk53!

I tested with the real data and there is an issue when the termination date is '12/31/2199', it picks it up regardless of the enrollment date being in 2013.

UNIVERSALMEMBERID ENROLLMENTDATE TERMINATIONDATE
7777 12/19/2011 10/31/2012
7777 1/1/2014 12/31/2199
8888 10/1/2013 12/31/2013
8888 1/1/2014 12/31/2199
9999 10/1/2012 10/8/2012
9999 10/9/2012 12/31/2199
10000 8/1/2013 9/11/2013
10000 9/12/2013 12/31/2013
10000 1/1/2014 12/31/2199
11111 1/1/2014 12/31/2199
22222 2/1/2013 8/31/2013
22222 9/1/2013 12/31/2013
22222 1/1/2014 12/31/2199
33333 1/1/2014 12/31/2199
44444 1/1/2014 12/31/2199
55555 9/1/2011 4/8/2012
55555 4/9/2012 1/31/2013
55555 2/1/2013 12/31/2013
55555 1/1/2014 12/31/2199
66666 10/1/2011 4/30/2012
66666 5/1/2012 1/31/2013
66666 2/1/2013 12/31/2013
66666 1/1/2014 12/31/2199
77777 8/30/2011 2/29/2012
77777 3/1/2012 8/31/2012
77777 9/1/2012 12/31/2199
88888 8/1/2011 5/31/2013
88888 6/1/2013 12/31/2199

Member 7777 shouldn't be included since there is no enrollment for 2013 at all.

Member 8888 shouldn't be included since enrollment starts in 10/1/2013 and doesn't meet requirement of being continuously enrolled during 2013 with only a 45 days gap.

Member 9999 is correctly included.

Member 10000 is the same case as 8888 and shouldn't be included.

Member 11111 shouldn't be included since enrollment is 1/1/2014 and termination is 12/31/2199.

Member 22222 is correctly included.

Members 33333 and 44444 shouldn't be included since enrollment is 1/1/2014 and termination is 12/31/2199.

Member 55555 is correctly included.

Member 66666 is correctly included.

Member 77777 is correctly included.

Member 88888 is correctly included.

We are almost there, but I'm not sure what to change to make this last step work properly to get rid of the ones that shouldn't be there.

Thanks a lot for your continuous help and your time spent on this!!!

Best regards!

Tammy

jtammyg1 · Jun 20, 2014

ok, I see what the problem is now.

Because I had to change from using member id to use universal memberid which can have people with different member ids, there are overlapps on the dates like this:

memberid universalmemberid enrollment termination
1111 1111SMITHJOHN 9/13/2011 5/31/2012
2222 1111SMITHJOHN 9/1/2012 12/31/2199
3333 1111SMITHJOHN 3/1/2013 6/30/2013
4444 1111SMITHJOHN 11/1/2013 11/11/2013
4444 1111SMITHJOHN 11/12/2013 12/31/2013
4444 1111SMITHJOHN 1/1/2014 12/31/2199

So really this case should be included too. I changed your query using universalmemberid, but it is not working in the instances explained in my previous post.

What do I need to change, please?

Thanks a lot, gk53!!!

Tammy

gk53 · Jun 23, 2014

Look on that

SQL:

declare @tbl as table
(
Memberid int,
enrollment date,
termination date

)
--insert into @tbl
--select 1111, '9/1/2009', '9/7/2009'
--insert into @tbl
--select 1111 , '9/8/2009', '9/30/2009'
--insert into @tbl
--select 2222 , '9/1/2009', '6/30/2010'
--insert into @tbl
--select 2222 , '7/1/2010', '4/30/2013'
--insert into @tbl
--select 2222 , '8/1/2013', '12/31/2013'
--insert into @tbl
--select 3333 , '11/1/2009', '11/3/2009'
--insert into @tbl
--select 3333 , '11/4/2009', '6/30/2010'
--insert into @tbl
--select 3333 , '7/1/2010', '12/31/2199'
--insert into @tbl
--select 4444 , '1/1/2013', '12/31/2013'
--insert into @tbl
--select 5555 , '11/1/2009', '11/1/2009'
--insert into @tbl
--select 6666 , '11/1/2009', '11/3/2009'
--insert into @tbl
--select 6666 , '11/4/2009', '3/31/2010'
--insert into @tbl
--select 6666 , '4/1/2010', '6/30/2010'
--insert into @tbl
--select 6666 , '7/1/2010', '8/31/2010'
--insert into @tbl
--select 6666 , '9/1/2010', '11/30/2010'
--insert into @tbl
--select 6666 , '12/1/2010', '4/30/2013'
--insert into @tbl
--select 6666 , '5/1/2013', '8/31/2013'

insert into @tbl
select 7777 , '12/19/2011', '10/31/2012'
insert into @tbl
select 7777 , '1/1/2014', '12/31/2199'
insert into @tbl
select 8888 , '10/1/2013', '12/31/2013'
insert into @tbl
select 8888 , '1/1/2014', '12/31/2199'
insert into @tbl
select 9999 , '10/1/2012', '10/8/2012'
insert into @tbl
select 9999 , '10/9/2012', '12/31/2199'
insert into @tbl
select 10000 , '8/1/2013', '9/11/2013'
insert into @tbl
select 10000 , '9/12/2013', '12/31/2013'
insert into @tbl
select 10000 , '1/1/2014', '12/31/2199'
insert into @tbl
select 11111 , '1/1/2014', '12/31/2199'
insert into @tbl
select 22222 , '2/1/2013', '8/31/2013'
insert into @tbl
select 22222 , '9/1/2013', '12/31/2013'
insert into @tbl
select 22222 , '1/1/2014', '12/31/2199'
insert into @tbl
select 33333 , '1/1/2014', '12/31/2199' 
insert into @tbl
select 44444 , '1/1/2014', '12/31/2199' 
insert into @tbl
select 55555 , '9/1/2011', '4/8/2012'
insert into @tbl
select 55555 , '4/9/2012', '1/31/2013'
insert into @tbl
select 55555 , '2/1/2013', '12/31/2013'
insert into @tbl
select 55555 , '1/1/2014', '12/31/2199'
insert into @tbl
select 66666 , '10/1/2011', '4/30/2012'
insert into @tbl
select 66666 , '5/1/2012', '1/31/2013'
insert into @tbl
select 66666 , '2/1/2013', '12/31/2013'
insert into @tbl
select 66666 , '1/1/2014', '12/31/2199'
insert into @tbl
select 77777 , '8/30/2011', '2/29/2012'
insert into @tbl
select 77777 , '3/1/2012', '8/31/2012'
insert into @tbl
select 77777 , '9/1/2012', '12/31/2199'
insert into @tbl
select 88888 , '8/1/2011', '5/31/2013'
insert into @tbl
select 88888 , '6/1/2013', '12/31/2199'




;with cte as (
select activeMembers = Memberid,
			enrollments = COUNT(*)
	from @tbl
	where termination >= '12/31/2013'
	group by Memberid	
)
select distinct inclide.Memberid
	from 
	(select t1.*, gap = DATEDIFF(day, t1.termination, isnull(t2.enrollment,'12/31/3999')), nextEnrollment = isnull(t2.enrollment,'12/31/3999')
	from (
		select  row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
				Memberid,
				enrollment,
				termination,
				c.enrollments
		from @tbl  t
		join cte c
		on t.Memberid = c.activeMembers
		) t1
		left join (
				select row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
						Memberid,
						enrollment,
						termination,
						c.enrollments
				from @tbl  t
				join cte c
				on t.Memberid = c.activeMembers
				) t2
		on t1.Memberid = t2.Memberid
		and t1.row + 1 = t2.row 
		) as inclide
	left join 
		(select t1.*, gap = DATEDIFF(day, t1.termination, isnull(t2.enrollment,'12/31/3999')), nextEnrollment = isnull(t2.enrollment,'12/31/3999')
		from (
			select  row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
					Memberid,
					enrollment,
					termination,
					c.enrollments
			from @tbl  t
			join cte c
			on t.Memberid = c.activeMembers
			) t1
			left join (
					select row = ROW_NUMBER() over(PARTITION by Memberid order by Memberid, enrollment ),
							Memberid,
							enrollment,
							termination,
							c.enrollments
					from @tbl  t
					join cte c
					on t.Memberid = c.activeMembers
					) t2
			on t1.Memberid = t2.Memberid
			and t1.row + 1 = t2.row 
			where   DATEDIFF(day, t1.termination, isnull(t2.enrollment,t1.termination)) > 45
			and (t1.termination between '1/1/2013' and '12/31/2013' 
			or isnull(t2.enrollment,t1.termination) between '1/1/2013' and '12/31/2013' 
			)
			--or t1.enrollment < '1/1/2013' and  t1.termination > '12/31/2013'
			) as exclude
	on inclide.Memberid = exclude.Memberid
	where 1 = case when inclide.enrollments = 1 and (inclide.enrollment > '12/31/2013' or inclide.termination < '1/1/2013') then 0
					else 1 end
	and  exclude.Memberid is null

I added one more missing condition in final where clause to exclude records where only one enrolment and it is outside of year 2013
where 1 = case when inclide.enrollments = 1 and (inclide.enrollment > '12/31/2013' or inclide.termination < '1/1/2013') then 0
else 1 end

jtammyg1 · Jun 23, 2014

Did you see my comment on using universalmemberid instead of the memberid and how some of the dates overlap?

I will test the above with the real data and get back to you asap.

Thank you so much for your help, GK53! [glasses]

jtammyg1 · Jun 23, 2014

GK53,

The following cases are not being picked up and they should be picked up. You see that universalmemberid 78901ROBERTJOHNSON has overlapping dates but they are within 1/1/2013 and 12/31/2199, which in this case is correct:

UNIVERSALMEMBERID ENROLLMENT TERMINATION

12345LARRYSMITH 1/1/2013 3/31/2013
12345LARRYSMITH 4/1/2013 12/31/2013
12345LARRYSMITH 1/1/2014 12/31/2199

234567MARYDOE 1/1/2013 12/31/2199

56789JOHNTDOE 1/1/2013 3/4/2013
56789JOHNTDOE 3/5/2013 12/31/2199

78901ROBERTJOHNSON 9/1/2012 12/31/2199
78901ROBERTJOHNSON 3/1/2013 6/30/2013
78901ROBERTJOHNSON 11/1/2013 11/11/2013
78901ROBERTJOHNSON 11/12/2013 12/31/2013

These cases are being included, but they should not be included at all. The first 2 records for that member start on 10/1/2013 until 12/31/2013, which is not the entire year for 2013 minus the 45 day allowed gap. The same applies to universalmemberid 22134RITADILLON, it shouldn't be included at all:

19920JACKMCMILLAN 10/1/2013 12/31/2013
19920JACKMCMILLAN 1/1/2014 12/31/2199

22134RITADILLON 8/1/2013 9/11/2013
22134RITADILLON 9/12/2013 12/31/2013

44324LAURIEHOUSE 11/1/2013 12/31/2013

How can we fix this parts, please, GK53?

Thank you so very much!!!

Tammy

gk53 · Jun 23, 2014

Is UNIVERSALMEMBERID goes instead of memberid or you using both? You never mention about not entire year... And what rules you using for overlapping records?

jtammyg1 · Jun 23, 2014

universalmemberid goes instead of the memberid. The entire year (2013) with only one gap of 45 days is required for continuous enrollment.

I had to change to universalmemberid, because some members have more than one member id and that is the overlapping you are seeing.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Pulling only members who have no gaps between dates and &lt;= 45 days non continuos enrollment

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

MIS

Programmer

MIS

MIS

Programmer

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor

Pulling only members who have no gaps between dates and <= 45 days non continuos enrollment