-
how to
Hi all,
I am having difficulties with resolving the following situation. I have a table as below:
client_id firstname lastname
ab11111 john doe
as11111 john doe
cl22222 jackie smith
pa22222 jackie smith
ct33333 john smith
ah33333 john smith
as55555 robert moore
ab55555 robert moore
ah66666 gkj kljlk
ct66666 gkj kljlk
I need to create an output which will show the duplicate records as below:
Client1 client2 ssn
ab as 11111
ab as 55555
cl pa 22222
ct ah 33333
ct ah 66666
How would I be able to do it??
It shouldn't show :
Client1 client2 ssn
ab as 11111
ab as 55555
cl pa 22222
ct ah 33333
ct ah 66666
as ab 11111
as ab 55555
ah ct 33333
ah ct 66666
Please help.....
Thanks.
-
create table client (client_id varchar(100), firstname varchar(100),lastname varchar(100))
insert into client select 'ab11111',' john',' doe'
insert into client select 'as11111',' john',' doe'
insert into client select 'cl22222',' jackie',' smith'
insert into client select 'pa22222',' jackie',' smith'
insert into client select 'ct33333',' john',' smith'
insert into client select 'ah33333',' john',' smith'
insert into client select 'as55555',' robert',' moore'
insert into client select 'ab55555',' robert',' moore'
insert into client select 'ah66666',' gkj',' kljlk'
insert into client select 'ct66666',' gkj',' kljlk '
--First step
create view x as
select
left (A.Client_id,2) as Client1,
left (A.Client_id,2) as Client2,
right(A.Client_id,5) as SSN
from client A ,
(select firstname,lastname, count(*) as COUNT from client group by
firstname,lastname) as B
where a.firstname=b.firstname and a.lastname=b.lastname
--Select View with cross tab query
SELECT SSN,
MAX(CASE COUNT1 WHEN 1 THEN Client1 ELSE ''END) AS MyClient1,
MAX(CASE COUNT1 WHEN 2 THEN Client1 ELSE ''END) AS MyClient2
FROM
(select client1,client2,ssn,count1=
Case when ssn = ssn then
(select count(*) from x a where
a.ssn=b.ssn and
a.client1+a.SSN < b.client1+b.SSN)+1 end
from x b) as MyTable
GROUP BY SSN
--You can do without using view. it would be a huge query. I did'nt had time to optimize it. Anybody like to optimize this query, please feel free to post it.
-
--simple query
select min(left(client_id,2)) as first_id
, max(left(client_id,2)) as last_id
, right(client_id,5) as client_id
, count(*) as records -- to see if there're more then two records
--, firstname , lastname
from client
group by right(client_id,5) --, firstname , lastname
having count(*) > 1 -- show only duplicates
You Have To Be Happy With What You Have To Be Happy With (KC)
-
Cooool.
-
Thanks guys, that helped. You guys are soooo smart.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|