Here’s a good trick to create a line sequence number for each invoice # that I found on the internet which help me on one of my projects.
First create a table. The seq column is used as the seed in building the sequence number for each invoice # line sequence number
create table #tmp
(
invoicenum int,
item char(5),
lineseq int,
seq int identity(1,1)
)
Then populate the table.
insert #tmp
select 100,’A’,0
insert #tmp
select 101,’A’,0
insert #tmp
select 101,’B’,0
insert #tmp
select 101,’C’,0
insert #tmp
select 102,’A’,0
insert #tmp
select 102,’B’,0
insert #tmp
select 103,’A’,0
Here’s what it looks like now in the table.
select invoicenum,item,lineseq from #tmp
invoicenum item lineseq
100 A 0
101 A 0
101 B 0
101 C 0
102 A 0
102 B 0
103 A 0
Then update the table with the following query statement
update #tmp set lineseq = (select count(*) from #tmp t1 wheret1.seq <= t.seq and t1.invoicenum = t.invoicenum)
from #tmp t
Here’s what it looks like after the update
select invoicenum,item,lineseq from #tmp
invoicenum item lineseq
100 A 1
101 A 1
101 B 2
101 C 3
102 A 1
102 B 2
103 A 1