Date between two or more records min and max dates

[thorny00]

For simplicity, I have a table – Claims that has memberid and dateof service. My other table – Elig has membered, mineffectivedate and maxeffectivedate. Now a member could have several records in the Elig table. I need to find records where the dateof service is not between any of the Elig records. Example –
Claims table
MemberID DateofService
123456 5/2/2011

Elig Table
MemberID MinEffectiveDate MaxEffective Date
123456 1/1/2011 4/1/2011
123456 6/1/2011 12/31/2011

Because there is a gap in the dates and the claim record is between that gap, I need that record. This is what I’ve started but it isn’t working correctly. Thanks!
select mc.MemberID
from dbo.tbl_MedicalClaims mc left outer join
dbo.vu_MemberEligibility me on mc.MemberID = me.MemberID and mc.FeedID = me.FeedID
where mc.FeedID = '1'
group by mc.MemberID, me.MinEffectiveDate, me.MaxTerminationDate,mc.DateOfService
having mc.DateOfService not between max(me.MinEffectiveDate) and min(me.MaxTerminationDate)

 

[bborissov]

DECLARE @Claims table (MemberID int, DateofService datetime)
INSERT INTO @Claims VALUES(123456, '20110502')

DECLARE @Elig Table(MemberID int,  MinEffectiveDate datetime, MaxEffectiveDate datetime)
INSERT INTO @Elig VALUES(123456,'20110101', '20110401')
INSERT INTO @Elig VALUES(123456,'20110601', '20111231')


SELECT Claims.*
FROM @Claims Claims
LEFT JOIN @Elig Elig ON Claims.MemberID = Elig.MemberID AND
                        Claims.DateofService BETWEEN Elig.MinEffectiveDate AND Elig.MaxEffectiveDate
WHERE Elig.MemberID IS NULL

Is that what you need?

Borislav Borissov
VFP9 SP2, SQL Server

 

[thorny00]

Sorry bborissov that is not what I need. The example I gave is just one record out of thousands. I need to look at each members memberid in tbl_Claims and see if there dateofservice falls between a gap of their records in tbl_Elig. A member can go out and in eligibility several times.

 

[Qik3Coder]

Why does that not work? That's exacly how I would have responded.

You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.

 

[bborissov]

Please post some example data and desired result from it.

Borislav Borissov
VFP9 SP2, SQL Server