Hi,

I've got a customer table with over 250,000 records. I need to "household" this customer table by combining records with matching criteria, such as same home phone number, or same address + city + postal code.

To do that the only thing I could think of was to create a cursor and go through every record, assign it a household number and then match all other records that should go with this record (if there are any) and give them the same number. Then move on to the next record. It doesn't work that well and takes almost 11 hours to run!!

Can anyone think of an elegant way of coding this? Getting rid of the cursor would be sooo great!

Pat

so, you have following columns in table user
id, firstname, lastname, address, city, zipcode, phone

and you want to add a 'householdid'

create a table household -> id, city, zipcode, phone. id has a seed.

   insert into household(city, zipcode, phone)
   select city, zipcode, phone
   from user
   group by city, zipcode, phone

all having same city, zipcode and phone will be created in household.

then create a householdid on the user table and update it.

update user set householdid = (select householdid from household where household.city = user.city and household.zip = user.zip and household.phone = user.phone)



this will assign a distinct household id to all households.

thanks

jigs

Thanks Jigs!

I figured out a way to make that work - waaaaay faster!!

Pat

Please post the solution!

Ok, here's what I did:

/* GROUP 1 */
SELECT CUSSUR, CUSAD1, CUSPST, COUNT(*) as 'Counts'
INTO [group1]
FROM [customer_new]
WHERE CUSSUR IS NOT NULL AND
      CUSAD1 IS NOT NULL AND
      CUSPST IS NOT NULL
GROUP BY CUSSUR, CUSAD1, CUSPST
ORDER BY 'Counts' DESC

/* GROUP 2 */
SELECT RPH, COUNT(*) as 'Counts'
INTO [group2]
FROM [customer_new]
WHERE RPH IS NOT NULL
GROUP BY RPH
ORDER BY 'Counts' DESC

The first groups by name, address, postal code. The second is by home phone number. Then I do the following to match it back to the customer table.

/* SET HOUSEHOLD IN CUSTOMER TABLE FROM GROUP 2 */
UPDATE a
SET a.household = b.household
FROM [customer_new] a, [group2] b
WHERE a.rph = b.rph

/* SET HOUSEHOLD IN GROUP 1 FROM CUSTOMER TABLE */
UPDATE c
SET c.household = a.household
FROM [group1] c, [customer_new] a
WHERE c.CUSSUR = a.CUSSUR AND
      c.CUSAD1 = a.CUSAD1 AND
      c.CUSPST = a.CUSPST

/* SET HOUSE IN CUSTOMER TABLE FROM GROUP 1 */
UPDATE a
SET a.household = c.household
FROM [customer_new] a, [group1] c
WHERE a.CUSSUR = c.CUSSUR AND
      a.CUSAD1 = c.CUSAD1 AND
      a.CUSPST = c.CUSPST

/* RECORDS WITH NULL HOUSEHOLD INTO GROUP 3 */
SELECT env_id
INTO [group3]
FROM [customer_new]
WHERE household IS NULL

/* SELECT MIN, MAX, DISTINCT */
SELECT MIN(household)
FROM [customer_new]

SELECT MAX(household)
FROM [group3]

SELECT COUNT(DISTINCT(household))
FROM [customer_new]

/* GROUP 3 IS THOSE RECORDS STILL WITHOUT A HOUSEHOLD */
SELECT *
FROM [group3]
WHERE household IN (SELECT household FROM customer_new)

I increment a household ID higher than those I have already set on the group 3 table, and then match back.

/* SET A UNIQUE HOUSEHOLD TO ALL THE GROUP3 THEN MATCH BACK TO CUSTOMER TABLE */
UPDATE a
SET a.household = b.household
FROM [customer_new] a, [group3] b
WHERE a.env_id = b.env_id

Thanks for posting your solution. It may be helpful to someone else.

Richard