[dba-SQLServer] Using the hash for real world problems

Hi John:

It looks good. One note is that I would put LName before FName as it might
produce more accurate results.

Jim



-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, March 19, 2011 6:56 AM
To: VBA; Sqlserver-Dba
Subject: [dba-SQLServer] Using the hash for real world problems

I got into the hashing business in order to create a surrogate key of sorts,
in fact a trio of them.

I have an address hash, a family hash and a person hash.  I compute these
three hashes for every 
table of persons.  Now that I have these surrogate keys I have "identical"
values in a single field 
where the input is identical in multiple fields.  Thus I do not have to do
multi-field joins where I 
have a hash for all of those fields.

I am happy to hear that I am secure in my surrogate hash algorithm choice.

OK, so I have these hashes.  Every record has these three hashes.

Now some definitions:

HashAddr: A unique address is addr/zip5/zip4 different
HashFamily: A unique family is LName/Addr/Zip5/Zip4 different
HahsPerson: A unique person is FName/LName/Addr/Zip5/Zip4 different.

Using this information, I need to calculate the count of addresses with a
single person, with two 
persons etc for as many combinations as I have.

Addresses	People
22,538,240	1
780,462,346	2
52,234		3 people
etc
etc till every quantity of people is found.

What is the simplest way to accomplish this with SQL.




-- 
John W. Colby
www.ColbyConsulting.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com