Generating UID where column value is x

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

General SQL Server Forums

New to SQL Server Programming

Generating UID where column value is x

Author

Topic

skoodog
Starting Member

5 Posts

Posted - 2011-08-09 : 16:07:37

Hey all,

I'm a SQL n00b in need of assistance. I'm trying to create a UID by location for a list of charges to use as an invoice number. In Excel-ese, basically i want to write =IF(C2=C1,A1,A1+1)...if that makes sense.

Here's an easy sample set:


create table #invoice
(
inv_no int,
id int,
location varchar(255),
amount int,
)

insert into #invoice (id, location, amount)
values (1, 'ABC',40)
insert into #invoice (id, location, amount)
values (2, 'ABC',30)
insert into #invoice (id, location, amount)
values (3, 'DEF',40)
insert into #invoice (id, location, amount)
values (4, 'GHI',70)

How would I write a SELECT statement so that location ABC has the same value in inv_no for both charges but it generates consecutive numerical values for DEF and GHI?

Thanks in advance!

EDIT: Changed question so it makes more sense.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 17:39:37

;WITH CTE (rn, location) AS (SELECT ROW_NUMBER() OVER (ORDER BY location), location FROM #invoice GROUP BY location)
UPDATE A SET inv_no=CTE.rn
FROM #invoice a INNER JOIN CTE ON a.location=CTE.location

SELECT * FROM #invoice

skoodog
Starting Member

5 Posts

Posted - 2011-08-10 : 16:15:23

quote:
Originally posted by robvolk

;WITH CTE (rn, location) AS (SELECT ROW_NUMBER() OVER (ORDER BY location), location FROM #invoice GROUP BY location)
UPDATE A SET inv_no=CTE.rn
FROM #invoice a INNER JOIN CTE ON a.location=CTE.location

SELECT * FROM #invoice

Sorry, I think I should have specified I am using SQL Server - T-SQL doesn't work for me. :(

Also I'm using SQL Server 2000 so I can't use a lot of advanced commands.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-10 : 16:19:25

quote:
Sorry, I think I should have specified I am using SQL Server - T-SQL doesn't work for me. :(

Ummmm, T-SQL DOES work on SQL Server, in fact it's the only language that does.

Are you using SQL Server 2000 (or earlier)? Or Sybase?

SQLTeam is a Microsoft SQL Server website. Take a look at http://dbforums.com/ if you need help with non-MS SQL Server products.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-10 : 16:30:10

DECLARE @CTE TABLE (rn INT NOT NULL IDENTITY(1,1) PRIMARY KEY, location VARCHAR(20) NOT NULL UNIQUE)
INSERT @CTE(location) SELECT DISTINCT location FROM #invoice ORDER BY location

UPDATE A SET inv_no=CTE.rn
FROM #invoice a INNER JOIN @CTE CTE ON a.location=CTE.location

SELECT * FROM #invoice

skoodog
Starting Member

5 Posts

Posted - 2011-08-11 : 12:31:56

quote:
Originally posted by robvolk

DECLARE @CTE TABLE (rn INT NOT NULL IDENTITY(1,1) PRIMARY KEY, location VARCHAR(20) NOT NULL UNIQUE)
INSERT @CTE(location) SELECT DISTINCT location FROM #invoice ORDER BY location

UPDATE A SET inv_no=CTE.rn
FROM #invoice a INNER JOIN @CTE CTE ON a.location=CTE.location

SELECT * FROM #invoice

You're amazing! Thanks, this worked perfectly!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources