Auto Number based on a Field Name

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 2005 Forums

Transact-SQL (2005)

Auto Number based on a Field Name

Author

Topic

jbosco1988
Starting Member

46 Posts

Posted - 2008-10-20 : 14:13:03

Is the a way to add AutoNumber based on a field name? example

Say i have the following table:

Invoice ID Charge Amount Transaction Number
12345` 110.00 1
12345 133.00 2
12345 140.00 3
12345 150.00 4
12346 110.00 1
12346 125.00 2
12346 134.00 3

How would I creat a Transaction Number based on the Invoice ID?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 14:16:11

[code]SELECT InvoiceID,
ChargeAmount,
ROW_NUMBER() OVER(PARTITION BY InvoiceID ORDER BY ChargeAmount) AS Transaction Number
FROM YourTable[/code]

jbosco1988
Starting Member

46 Posts

Posted - 2008-10-20 : 14:35:30

Ok How do I update my table with that the Transaction number?

Thanks So Much!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 14:43:03

quote:
Originally posted by jbosco1988

Ok How do I update my table with that the Transaction number?

Thanks So Much!!!!

UPDATE t
SET t.[Transaction Number]=tmp.[Transaction Number]
FROM YourTable t
INNER JOIN
(
SELECT InvoiceID,
ChargeAmount,
ROW_NUMBER() OVER(PARTITION BY  InvoiceID ORDER BY ChargeAmount) AS Transaction Number
FROM YourTable)tmp
ON t.InvoiceID=tmp.InvoiceID
AND t.ChargeAmount=tmp.ChargeAmount

jbosco1988
Starting Member

46 Posts

Posted - 2008-10-20 : 17:04:43

Ok that almost works except when update the Transaction number it keeps repeating 1 over and over again. The Select Statement works perfect.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-20 : 17:13:44

well, you can't use the InvoiceID because of duplication. Do you have a primary key column?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-20 : 17:26:27

According to Visa's solution:

create table #test (
Invoice_ID int,
Charge_Amount float,
Transaction_Number int
)
insert #test 
select 12345, 110.00, null union
select 12345, 133.00, null union
select 12345, 140.00, null union
select 12345, 150.00, null union
select 12346, 110.00, null union
select 12346, 125.00, null union
select 12346, 134.00, null

UPDATE t
SET t.[Transaction_Number]=tmp.[Transaction_Number]
FROM #test t
INNER JOIN
(
SELECT Invoice_ID,
Charge_Amount,
ROW_NUMBER() OVER(PARTITION BY  Invoice_ID ORDER BY Charge_Amount) AS Transaction_Number
FROM #test)tmp
ON t.Invoice_ID=tmp.Invoice_ID
AND t.Charge_Amount=tmp.Charge_Amount

select * from #test
drop table #test

results:
12345 110 1
12345 133 2
12345 140 3
12345 150 4
12346 110 1
12346 125 2
12346 134 3

Is it not, what you have expected?

Webfred

Planning replaces chance by mistake

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-20 : 17:43:58

However, if the real data contains Exact same Charge Amount then you'll need an Unique ID column.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-20 : 17:47:57

That's right!

Greetings
Webfred

Planning replaces chance by mistake

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-20 : 17:53:54

Added to Webfred's code:


create table #test (
Invoice_ID int,
Charge_Amount float,
Transaction_Number int
)
insert #test 
select 12345, 110.00, null union ALL
select 12345, 133.00, null union ALL
select 12345, 140.00, null union ALL
select 12345, 150.00, null union ALL
select 12346, 110.00, null union ALL
select 12346, 125.00, null union
select 12346, 134.00, null

--INSERT AGAIN TO DUP UP 
insert #test 
select 12345, 110.00, null union ALL
select 12345, 133.00, null union ALL
select 12345, 140.00, null union ALL
select 12345, 150.00, null union ALL
select 12346, 110.00, null union ALL
select 12346, 125.00, null union
select 12346, 134.00, null

alter table #test add ID uniqueidentifier not null default newid();

UPDATE t
SET t.[Transaction_Number]=tmp.[Transaction_Number]
FROM #test t
INNER JOIN
(
SELECT ID,Invoice_ID,
Charge_Amount,
ROW_NUMBER() OVER(PARTITION BY  Invoice_ID ORDER BY Charge_Amount) AS Transaction_Number
FROM #test)tmp
ON t.ID = tmp.ID
t.Invoice_ID=tmp.Invoice_ID
AND t.Charge_Amount=tmp.Charge_Amount

select * from #test
drop table #test

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-20 : 18:02:45

Well done,

at my location now it's 00:04 and i am going to sleep.

good n8

Webfred

Planning replaces chance by mistake

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-20 : 18:05:51

sweet dreams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 23:55:15


create table #test (
Invoice_ID int,
Charge_Amount float,
Transaction_Number int
)
insert #test 
select 12345, 110.00, null union ALL
select 12345, 133.00, null union ALL
select 12345, 140.00, null union ALL
select 12345, 150.00, null union ALL
select 12346, 110.00, null union ALL
select 12346, 125.00, null union
select 12346, 134.00, null

--INSERT AGAIN TO DUP UP 
insert #test 
select 12345, 110.00, null union ALL
select 12345, 133.00, null union ALL
select 12345, 140.00, null union ALL
select 12345, 150.00, null union ALL
select 12346, 110.00, null union ALL
select 12346, 125.00, null union
select 12346, 134.00, null

UPDATE t
SET t.Transaction_Number=t.Seq
FROM
(
SELECT Transaction_Number,Invoice_ID,
Charge_Amount,
ROW_NUMBER() OVER(PARTITION BY  Invoice_ID ORDER BY Invoice_ID) AS Seq
FROM #test
)t

select * from #test order by Invoice_ID,Transaction_Number
drop table #test


output
-------------------------------------
Invoice_ID  Charge_Amount          Transaction_Number
----------- ---------------------- ------------------
12345       110                    1
12345       133                    2
12345       140                    3
12345       150                    4
12345       110                    5
12345       133                    6
12345       140                    7
12345       150                    8
12346       110                    1
12346       125                    2
12346       134                    3
12346       110                    4
12346       125                    5
12346       134                    6

jbosco1988
Starting Member

46 Posts

Posted - 2008-10-24 : 17:45:05

Hey I just wanted to say thanks for every ones help!!!! Sorry it took me so long to get back on here, been a crazy busy week?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 02:23:01

quote:
Originally posted by jbosco1988

Hey I just wanted to say thanks for every ones help!!!! Sorry it took me so long to get back on here, been a crazy busy week?

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources