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
selectCustno, Addr1, City, Res_Phone, Bus_Phone, Fax_Phone, Marine_Phone, Pager_Phone,Other_Phone, email1, email2from customer cwhere Active=’Y’ — About 1.5 Million RecordsHere 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 groupidcustno2 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 groupIDso 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 Read More