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

Wait! Before You GoWould you like to talk to a business software expert to answer your questions?

When you schedule a free consultation with MIBAR, you will experience a one-on-one conversation with a business technology expert who is passionate about understanding your unique needs or issues. Schedule a free consultation today at the link below or give us a call at (212) 869-9300.

Learn More