Results 1 to 5 of 5

Thread: how to

  1. #1
    Join Date
    Oct 2002
    Location
    queens
    Posts
    139

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

  3. #3
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152
    --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)

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Cooool.

  5. #5
    Join Date
    Oct 2002
    Location
    queens
    Posts
    139
    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
  •