T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

T-SQL

Author

Topic

borntorun
Starting Member

1 Post

Posted - 2010-11-24 : 04:51:33

Hi there,

I'm wondering if someone on here could help me with some guidance on how I would go about doing the following based on this sample table data:

TableA
UniqueID Surname MothersBirthSurname DateOfBirth
1 Bloggs Jones 03/03/1990
2 Soap Smith 25/12/1970
3 Smith Collins 30/06/1975
4 Jones Fitzgerald 04/07/1976
5 Bloggs Jones 04/03/1990
6 Smith Collins 28/06/1975

What I am trying to achieve is select all records where the Surname and MothersBirthSurname are the same and where the DateOfBirth is within -2 or +2 days of each other.

I've been racking my brain on this for a while now and haven't been able to make much progress so am hoping that someone on here can at least start to send me in the right direction.

Any help given will be much appreciated...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-24 : 05:23:47

[code]-- creating a temp. table for testdata
create table #tableA(
UniqueID int,
Surname varchar(20),
MothersBirthSurname varchar(20),
DateOfBirth datetime)

-- insert testdata
insert #tableA
select 1,'Bloggs','Jones','19900303' union all
select 2, 'Soap', 'Smith', '19701225' union all
select 3, 'Smith', 'Collins', '19750630' union all
select 4, 'Jones', 'Fitzgerald', '19760704' union all
select 5, 'Bloggs', 'Jones', '19900304' union all
select 6, 'Smith', 'Collins', '19750628'

-- solution (just replace #tableA by your tablename)
select a.*
from #tableA as a
join #tableA as b
on a.Surname = b.Surname
and a.MothersBirthSurname = b.MothersBirthSurname
and a.UniqueID <> b.UniqueID
and datediff(day,a.DateOfBirth,b.DateOfBirth) between -2 and 2
order by a.Surname

-- cleaning testdata
drop table #tableA
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-24 : 05:26:57

Next time please do your own job when asking a question and post the create and insert statement like above so we can copy and paste that to work out the solution.
And please post your wanted output in relation to the testdata.

thx

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources