Assign Identity to a group of records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Assign Identity to a group of records

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-10-07 : 17:16:14

Is it possible to assign an identity number to a group of records, without using a cursor?

I have a table that has company names and addresses in it. I need to group these records by
company name and address (zip5, zip4, address1, and address2) - and assign each of those groups a unique number.

If the word 'inc' or 'incorported' etc are in the name then i need to ignore it and match on the rest of the name.
If address1 = address1, or address1 = address2 and the zips match, then it needs to be a match, regardless of company name.

Table Creation:
CREATE TABLE [dbo].[company] (
[rec_id] [int] IDENTITY (1, 1) NOT NULL ,
[co_name] [char] (30) NULL ,
[address1] [char] (30) NULL,
[address2] [char] (30) NULL,
[city] [char] (20) NULL,
[state] [char] (2) NULL,
[zip5] [char] (5) NULL,
[zip4] [char] (4) NULL,
[group_no] [int] null )
GO

Data:
insert company (co_name, address1, address2, city, state, zip5, zip4) values ('shell','10 main', '', 'phoenix', 'az', '85243', '3600')
insert company (co_name, address1, address2, city, state, zip5, zip4) values ('shell inc','', '10 main', 'phoenix', 'az', '85243', '3600')
insert company (co_name, address1, address2, city, state, zip5, zip4) values ('shell','10 main', '', 'chino', 'ca', '99555', '8563')
insert company (co_name, address1, address2, city, state, zip5, zip4) values ('gold','16 porter', '', 'tempe', 'az', '85255', '9850')
insert company (co_name, address1, address2, city, state, zip5, zip4) values ('gold','16 porter', '', 'tempe', 'az', '85255', '9850')
insert company (co_name, address1, address2, city, state, zip5, zip4) values ('fast grill','8500 west mill', '', 'mesa', 'az', '85240', '3966')

In the above cases, the first 2 records should be grouped together because the have the same name and address, but
the 3rd won't be grouped because it is in a different zip code. The 4th and 5th records would be grouped because they are the same
address.

Thanks for any help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-07 : 17:30:01

Your first and second records have a different name, one is shell the other is shell inc. How would you know that this is the same company?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-07 : 17:33:47

Is this what you want:

create table #temp
(Ident int IDENTITY(1, 1) NOT NULL,
co_name varchar(30))

insert into #temp (co_name)
select co_name from company
group by co_name, address1, address2, zip4, zip5

select * from #temp

drop table #temp

Also, do not use CHAR for these columns: address1, address2, and city. Only use CHAR for columns that will always have that length. For state, you know it will always be 2, so you put CHAR(2), which is correct. But you don't know what it would be for address1, address2, and city, so use VARCHAR instead.

Tara

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-10-07 : 17:34:48

Part of my requirement is that I need to drop the words INC or INCORPORATED from the company name while matching, since SHELL and SHELL INC should be treated as the same.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-07 : 17:36:39

You could use the REPLACE function to remove INC or INCORPORATED.

Tara

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-10-07 : 17:50:49

That isn't quite what I need. I need the group_no field to be populated. Also "If address1 = address1, or address1 = address2 and the zips match, then it needs to be a match, regardless of company name."

Thanks for the help so far.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 10:05:51

bad data is bad data...how many rows do you have?

I would hazard a guess that sql can only take you so far...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources