We help IT Professionals succeed at work.

Identify Member Id's where there was a gap within there membership date

tyruss8
tyruss8 asked
on
Hello,

I've been trying to solve this for a while but I need some expert help. Attached will find a table "Members".
I'm trying to identify Member ID's, where there were gaps in their Start and End dates. For example, if I were to look at this table manually, Member ID's (J003, A001 and C005) would be flagged or identify because there was a gap in there membership, or it was not continuous for following reasons:

-J003 = Record 6-7 (start 7/1/2014 - end 7/31/2014; start 10/1/2014 - end 10/31/2014). For this scenario it went from end 7/31/2014 to start again to 10/1/2014, thus there was a gap.

-A001 = Record 11-12 (start 2/1/2015 -  end 3/31/2015; start 5/1/2015 - end 12/31/9999). For this scenario it went from end 3/31/2015 to 5/1/2015, thus again the gap.

So what I really need to do is idenitify the Member ID where there was a gap in their coverage.

Thank you in advance!
YG
Database21.accdb
Comment
Watch Question

Jeff DarlingDeveloper Analyst

Commented:
Attached is a Query with a function to check if Member has a gap in the membership.
DataGapTest.accdb
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I cannot open your file at the moment, but here is the technique I generally use:

SELECT T3.ID, T3.StartDate, T3.EndDate, T3.NextStart
FROM (
SELECT T1.ID, T1.StartDate, T1.EndDate, Min(T2.StartDate) as NextStart
FROM yourTable T1
LEFT JOIN yourTable T2
ON T1.ID = T2.ID AND T1.EndDate <T2.StartDate
GROUP BY T1.ID, T1.StartDate, T1.EndDate
) as T3
WHERE NZ(T3.NextStart, Date()) - T3.EndDate > 2

You might have to play with the WHERE clause to get just the records you want.  For example, you only want those where there is no NextStart, in which case, you would use:

WHERE T3.NextStart is NULL
Jeff DarlingDeveloper Analyst

Commented:
Is this error on purpose?

Start Date is greater than end date.

Member ID	START	END
B005	2014-09-01	2014-08-25

Open in new window

Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
I did something slightly different:

1. Got a list of policies that had something before or after them (qryOverlaps).

SELECT Members.[Member ID], Members.START
FROM Members INNER JOIN Members AS Members_1 ON Members.[Member ID] = Members_1.[Member ID]
WHERE Members.START=[Members_1]![END]+1 OR Members.END=[Members_1]![Start]-1)
GROUP BY Members.[Member ID], Members.START;

2. Got a count of the number of policies each member had (qryPolicyCount):

SELECT Members.[Member ID], Count(Members.[Member ID]) AS Expr1
FROM Members
GROUP BY Members.[Member ID];


3. Last, the list of those members where a given policy didn't have something before or after it and they had more than one policy (qryGaps):

SELECT Members.[Member ID], Members.START, Members.END, qryOverlaps.[Member ID], qryPolicyCount.Expr1
FROM (Members LEFT JOIN qryOverlaps ON (Members.START = qryOverlaps.START) AND (Members.[Member ID] = qryOverlaps.[Member ID])) INNER JOIN qryPolicyCount ON Members.[Member ID] = qryPolicyCount.[Member ID]
WHERE (((qryOverlaps.[Member ID]) Is Null) AND ((qryPolicyCount.Expr1)>1));

Jim.
Top Expert 2016

Commented:
try this single query

Select A.[Member ID],A.Start,A.End,A.nStart, IIF(A.Days > 1, "Yes","No") as Flag
From
(
SELECT Members.[Member ID], Members.START, Members.END, (SELECT Min(M.START) AS MinOfSTART FROM Members AS M WHERE M.[member id]=[Members].[member id] And M.Start>=Members.End) AS nStart, DateDiff("d",[End],[nStart]) AS Days
FROM Members
ORDER BY Members.[Member ID], Members.START
) As A

Author

Commented:
Hi Jeff,

I ran your query and looked at the module and for the most part it's correct. But the query result for B005 = "No", where it should be a "Yes", any ideas?

Thanks

Author

Commented:
Wow, thanks for all the response I will be trying everyone suggestions later on the day, I appreciate it!

Thanks

Author

Commented:
Sorry Jeff, no it was not in purpose was a typo on my end. Thus, I think your db is spot on. I will do multiple test with correct data.

Thank you
Jeff DarlingDeveloper Analyst

Commented:
code error.

here is a new one.
DataGapTest.accdb
Jeff DarlingDeveloper Analyst

Commented:
first solution was broke, this one fixes an error.  It was not related to the bad data issue.

Author

Commented:
Hi Jeff,

I ran query qryGapList and J001 = Yes when it should be No, but everything else was correct.
Developer Analyst
Commented:
coding error again.

fixed
DataGapTest.accdb
Top Expert 2016
Commented:
@tyruss8

did you try the query i posted at http:#a40636084 ?

just copy and paste

Author

Commented:
Thank you all for responding, appreciate it agaig. I'm going to dissect and learn these coding techniques, I really like them all.

Thank you Jeff for the quick response, going to close this out should be good.

Hi Rey,
Yes, I did finally copy and paste it, and works like a charm too, man you guys are amazing.

Thank you all!!