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


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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff DarlingDeveloper AnalystCommented:
Attached is a Query with a function to check if Member has a gap in the membership.
Dale FyeOwner, Developing Solutions LLCCommented:
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
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 AnalystCommented:
Is this error on purpose?

Start Date is greater than end date.

B005	2014-09-01	2014-08-25

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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));

Rey Obrero (Capricorn1)Commented:
try this single query

Select A.[Member ID],A.Start,A.End,A.nStart, IIF(A.Days > 1, "Yes","No") as Flag
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
tyruss8Author 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?

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

tyruss8Author 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 AnalystCommented:
code error.

here is a new one.
Jeff DarlingDeveloper AnalystCommented:
first solution was broke, this one fixes an error.  It was not related to the bad data issue.
tyruss8Author Commented:
Hi Jeff,

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:

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

just copy and paste
tyruss8Author 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!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.