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
Open for Business – The Path to Recovery

This webinar is focused on helping leaders plan for the future and adapt their business model as the economy reopens.

Join MIBAR as we host guest panelist Gary Bettan, President of Broadfield Distributing Inc., Founder and President of Videoguys, and a digital transformation expert who has helped startups and mid-market leaders future-proof their business in an ever-changing competitive landscape.