Problem with Select...

[Deam]

Here is the problem:
I need to find out a list of Employees who have a benefit record with an end date of 12/31/2006 AND have a benefit record starting 1/1/2007 AND Had dependents enrolled in the 12/31/2006 record BUT do not have those dependents enrolled in the 1/1/2007 record.
(My client forgot to enroll the dependents in the 2007 record and accidentally enddated their 2006 records)
Tables:tPBenefitHist(PGUID,ResponsiblePGUID,StartDate,EndDate,PlanCode,OptionCode,CurrentFlag) -
[All people enrolled in a benefit have to have a record in tPBenefitHist(Employees and Dependents) - so John Doe and his dependents who were enrolled till 12/31/2006 have a record in this table]. Example:
PGUID ResponsiblePGUID StartDate EndDate PlanCode OptionCode CurrentFlag
11111 11111 1/1/2007 null ABD Family 1 - John Doe's record
11111 11111 3/4/2001 12/31/2006 ABD Family 0 - John Doe's record
33333 11111 3/4/2001 12/31/2006 ABD Family 0 - his son Joe's record
44444 11111 3/4/1001 12/31/2006 ABD Family 0 - his spouse Jane's record
tDependent(DepPGUID,PGUID,FName,LName) - This table will have the Dependents' info like below:
DepPGUID PGUID FName LName
33333 11111 Joe Doe
44444 11111 Jane Doe
tPerson(PGUID,FName,LName) - This is the main table that contains person info like belowThis table is mainly used to get the name)
PGUID FName LName
11111 John Doe
33333 Joe Doe
44444 Jane Doe
Please help...I am going around in circles and not getting anywhere...

 

[bborissov]

Can you post some data just to see how a normal records must look like? Because I saw only the records which must be in your query, but I need to know how a records that must be filtered also look like.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP