Dates Subquery

[James Jones]

Hi,

I am having fun trying to get a subquery to return what I want it to.
Tried searching google groups but apparently I am asking it the wrong
question as I can't find an example close enough to what I would like.

I have some inherited data (that is not normalised which is what is
making it hard) of customers and properties they have rented at various
times. In some cases they ahve transfered from one property to another.
The tenancies table has TenancyRef, Property Ref and Start and End Date.
The people table has PersonRef, TenancyRef, Forename and Surname.

What I am trying to do is return records that show PersonRef,
PropertyRef, TenancyRef, Forename, Surname, StartDate and EndDate which
is a bog standard query joined on the TenancyRef but I want them in
Forename, Surname and StartDate order so I can make a stab of finding
those pepople that have transfered from one property to another (and
therefore another tenancy) but have multiple people records. Probably
some example data may help

PersonRef,PropertyRef,TenancyRef,Forename,SurnameRef,StartDate,EndDate
787654, 11111007, 123456, Steve, Smith, 14/06/2000, 29/05/2002
717865, 40056089, 123669, Steve, Smith, 25/01/2001, 23/10/2005
777457, 11000123, 123999, Steve, Smith, 30/05/2002, 28/02/2007
777457, 34218665, 125123, Steve, Smith, 01/03/2007, NULL

A null in the end date meeans they still live there. In the above
example there is a real good chance that the 1st, 3rd and 4th records are
the same person and we already know that the 3rd and 4th record is the
same person but there is no guarantee that this will always happen.

I think I need to use a subquery to look at previous and next records so
I only return those that are consecutive ie. in the example above the 2nd
records dates do not follow the first and do not lead to the 3rd so I
want to ignore this record and thousands of others like it. (If I just do
a duplicate query for forename and surname I get over 5000 matches which
is too many to eyeball)

Problem is I am trying to use

WHERE a.[StartDate] - 1 = b.[EndDate]

in my subquery and I am begining to suspect this is the wrong approach.

Anyone got a better idea how I can return those people with same first
and last name with consecutive dates?

BTW the dates are in UK format, sorry if it confuses anyone.

TIA J. Jones

 

[Allen Browne]

If the data lacks a primary key, you will need one to refer to the prior
record. This example assumes a primary key named ID:

SELECT Table1.*,
(SELECT ID FROM Table1 AS Dupe
WHERE Dupe.Forename = Table1.ForeName
AND Dupe.SurnameRef = Table1.ForeNameRef
AND Dupe.EndDate - Table1.EndDate Between 0 And 1) AS PriorID)
FROM Table1;

If you really want to limit it to only the prior record, you would need to
read the prior ID value, and limit it to that one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am having fun trying to get a subquery to return what I want it to.
Tried searching google groups but apparently I am asking it the wrong
question as I can't find an example close enough to what I would like.

I have some inherited data (that is not normalised which is what is
making it hard) of customers and properties they have rented at various
times. In some cases they ahve transfered from one property to another.
The tenancies table has TenancyRef, Property Ref and Start and End Date.
The people table has PersonRef, TenancyRef, Forename and Surname.

What I am trying to do is return records that show PersonRef,
PropertyRef, TenancyRef, Forename, Surname, StartDate and EndDate which
is a bog standard query joined on the TenancyRef but I want them in
Forename, Surname and StartDate order so I can make a stab of finding
those pepople that have transfered from one property to another (and
therefore another tenancy) but have multiple people records. Probably
some example data may help

PersonRef,PropertyRef,TenancyRef,Forename,SurnameRef,StartDate,EndDate
787654, 11111007, 123456, Steve, Smith, 14/06/2000, 29/05/2002
717865, 40056089, 123669, Steve, Smith, 25/01/2001, 23/10/2005
777457, 11000123, 123999, Steve, Smith, 30/05/2002, 28/02/2007
777457, 34218665, 125123, Steve, Smith, 01/03/2007, NULL

A null in the end date meeans they still live there. In the above
example there is a real good chance that the 1st, 3rd and 4th records are
the same person and we already know that the 3rd and 4th record is the
same person but there is no guarantee that this will always happen.

I think I need to use a subquery to look at previous and next records so
I only return those that are consecutive ie. in the example above the 2nd
records dates do not follow the first and do not lead to the 3rd so I
want to ignore this record and thousands of others like it. (If I just do
a duplicate query for forename and surname I get over 5000 matches which
is too many to eyeball)

Problem is I am trying to use

WHERE a.[StartDate] - 1 = b.[EndDate]

in my subquery and I am begining to suspect this is the wrong approach.

Anyone got a better idea how I can return those people with same first
and last name with consecutive dates?

BTW the dates are in UK format, sorry if it confuses anyone.

TIA J. Jones