Forum Discussion

buzza1234's avatar
buzza1234
Copper Contributor
Apr 26, 2024

How to compare data fields with in customer tablle


select
Custno, Addr1, City, Res_Phone, Bus_Phone, Fax_Phone, Marine_Phone, Pager_Phone,
Other_Phone, email1, email2
from customer c
where Active='Y' -- About 1.5 Million Records


Here in this sql server table, I have customers table, custno is unique id.

we are trying to grouping customers and give one ID lets call it groupingID.

INSERT INTO Customer (Custno, Addr1, City, Res_Phone, Bus_Phone, Fax_Phone, Marine_Phone, Pager_Phone, Other_Phone, email1, email2)
VALUES
(1, '123 Main St', 'New York', '123-456-7890', '987-654-3210', '111-222-3333', '444-555-6666', '777-888-9999', '000-111-2222', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(2, '456 Elm St', 'Los Angeles', '456-789-0123', '012-345-6789', '222-333-4444', '555-666-7777', '888-999-0000', '111-222-3333', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(3, '123 Main St', 'New York', '789-012-3456', '234-567-8901', '333-444-5555', '666-777-8888', '999-000-1111', '222-333-4444', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(4, '321 Pine St', 'Houston', '890-123-4567', '345-678-9012', '444-555-6666', '777-888-9999', '000-111-2222', '333-444-5555', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(5, '654 Cedar St', 'Phoenix', '901-234-5678', '890-123-4567', '555-666-7777', '888-999-0000', '111-222-3333', '444-555-6666', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(6, '987 Maple St', 'Philadelphia', '333-444-5555', '567-890-1234', '666-777-8888', '999-000-1111', '222-333-4444', '555-666-7777', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(7, '210 Walnut St', 'San Antonio', '123-456-7890', '678-901-2345', '777-888-9999', '000-111-2222', '333-444-5555', '666-777-8888', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(8, '543 Birch St', 'San Diego', '234-567-8901', '789-012-3456', '888-999-0000', '111-222-3333', '444-555-6666', '777-888-9999', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(9, '876 Redwood St', 'Dallas', '345-678-9012', '890-123-4567', '999-000-1111', '222-333-4444', '555-666-7777', '888-999-0000', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(10, '1091 Poplar St', 'Austin', '456-789-0123', '901-234-5678', '000-111-2222', '333-444-5555', '666-777-8888', '999-000-1111', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(11, '121 Pineapple St', 'Seattle', '567-890-1234', '012-345-6789', '111-222-3333', '444-555-6666', '777-888-9999', '000-111-2222', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(12, '231 Cherry St', 'Portland', '678-901-2345', '123-456-7890', '222-333-4444', '555-666-7777', '888-999-0000', '111-222-3333', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(13, '344 Oakwood St', 'Miami', '789-012-3456', '234-567-8901', '333-444-5555', '666-777-8888', '999-000-1111', '222-333-4444', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(14, '456 Maplewood St', 'Tampa', '890-123-4567', '345-678-9012', '444-555-6666', '777-888-9999', '000-111-2222', '333-444-5555', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
(15, '567 Pine St', 'Orlando', '901-234-5678', '456-789-0123', '555-666-7777', '888-999-0000', '111-222-3333', '444-555-6666', 'email address removed for privacy reasons', 'email address removed for privacy reasons');

Custno 1 & 3 Adress & City match, custno3(faxphone) & custno6 (resphone) matching so these three records should match and get same groupid
custno2 record fields not match with any fields so that one will get one groupid.
custno4 & custno5 matches with res phone & business phone so these both reecords get same groupID

so any phone no filed matches with any other customers any other field consider as matched.

any email1 or email2 matched with any other customer email then all matched customer no get same groupID.


Please let me know if I need to provide any more information from myend.

Thank you in advance

 

  • olafhelper's avatar
    olafhelper
    Bronze Contributor

     


    Please let me know if I need to provide any more information from myend.

    buzza1234 , your post is hard to read, I can't see where code ends and a may exististing question starts. For code please use the code block.

     

    • buzza1234's avatar
      buzza1234
      Copper Contributor

      olafhelper 

       

      Sorry for that please see code block here.

       

      CREATE TABLE #Customers1 (Custno int, Addr1 varchar(50), City varchar(50), Res_Phone varchar(15), Bus_Phone varchar(15), Fax_Phone varchar(15), Marine_Phone varchar(15), Pager_Phone varchar(15), Other_Phone varchar(15), email1 varchar(50), email2 varchar(50))
      
      INSERT INTO #Customers1 (Custno, Addr1, City, Res_Phone, Bus_Phone, Fax_Phone, Marine_Phone, Pager_Phone, Other_Phone, email1, email2)
      VALUES
      (1, '123 Main St', 'New York', '123-456-7890', '987-654-3210', '111-222-3333', '444-555-6666', '777-888-9999', '000-111-2222', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),  
      (2, '456 Elm St', 'Los Angeles', '456-789-0123', '012-345-6789', '222-333-4444', '555-666-7777', '888-999-0000', '111-222-3333', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (3, '123 Main St', 'New York', '789-012-3456', '234-567-8901', '333-444-5555', '666-777-8888', '999-000-1111', '222-333-4444', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (4, '321 Pine St', 'Houston', '890-123-4567', '345-678-9012', '444-555-6666', '777-888-9999', '000-111-2222', '333-444-5555', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (5, '654 Cedar St', 'Phoenix', '901-234-5678', '890-123-4567', '555-666-7777', '888-999-0000', '111-222-3333', '444-555-6666', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (6, '987 Maple St', 'Philadelphia', '333-444-5555', '567-890-1234', '666-777-8888', '999-000-1111', '222-333-4444', '555-666-7777', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (7, '210 Walnut St', 'San Antonio', '123-456-7890', '678-901-2345', '777-888-9999', '000-111-2222', '333-444-5555', '666-777-8888', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (8, '543 Birch St', 'San Diego', '234-567-8901', '789-012-3456', '888-999-0000', '111-222-3333', '444-555-6666', '777-888-9999', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (9, '876 Redwood St', 'Dallas', '345-678-9012', '890-123-4567', '999-000-1111', '222-333-4444', '555-666-7777', '888-999-0000', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (10, '1091 Poplar St', 'Austin', '456-789-0123', '901-234-5678', '000-111-2222', '333-444-5555', '666-777-8888', '999-000-1111', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (11, '121 Pineapple St', 'Seattle', '567-890-1234', '012-345-6789', '111-222-3333', '444-555-6666', '777-888-9999', '000-111-2222', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (12, '231 Cherry St', 'Portland', '678-901-2345', '123-456-7890', '222-333-4444', '555-666-7777', '888-999-0000', '111-222-3333', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (13, '344 Oakwood St', 'Miami', '789-012-3456', '234-567-8901', '333-444-5555', '666-777-8888', '999-000-1111', '222-333-4444', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (14, '456 Maplewood St', 'Tampa', '890-123-4567', '345-678-9012', '444-555-6666', '777-888-9999', '000-111-2222', '333-444-5555', 'email address removed for privacy reasons', 'email address removed for privacy reasons'),
      (15, '567 Pine St', 'Orlando', '901-234-5678', '456-789-0123', '555-666-7777', '888-999-0000', '111-222-3333', '444-555-6666', 'email address removed for privacy reasons', 'email address removed for privacy reasons');

       

      Thanks in advance

      Buzza

    • buzza1234's avatar
      buzza1234
      Copper Contributor
      Can you please help here olafhelper. Thank you

      so when ever the phones matches (any customers any phone nos ) then assign one generic no.

      if email 1 or email 2 matches with any other customer group. but for addr1 and city both has to match with other customer.

      Thank you

Resources