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