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.
| Author |
Topic |
|
glilley
Starting Member
10 Posts |
 Posted - 2011-02-09 : 16:09:05
|
| I anticipate having to do something like this for my VAR who is working to set up an automated invoicing process in our ERP. I have a SQL view pulling data from an order header and order detail table where each detail item being invoiced will generate a row in the view. If an order being invoiced has 10 detail items then 10 rows will show in the view with header data being duplicated of course (key between tables is OrderNo). What I need to do is populate an "InvoiceNo" field in the view with the the current invoice number + 1 when the order number changes. The very first invoice number to be used will be pulled from a system field "LastInvoiceNumber", so the first order will take care of itself but when the next order data pulls in need to generate the next invoice number up. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-02-09 : 21:54:41
|
if you are using SQL 2005 / 2008, you can use dense_rank() over (order by OrderNumber)
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
glilley
Starting Member
10 Posts |
Posted - 2011-02-10 : 15:19:22
|
| Well, currently using SQL 2000 but, when ERP upgrade goes in summer will be at SQL 2008. |
|
|
|
glilley
Starting Member
10 Posts |
Posted - 2011-02-11 : 07:56:47
|
| Any suggestions for SQL 2000? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-02-11 : 08:19:40
|
you can make use of a temp table with identity. Insert the records into the temp table and select back.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
glilley
Starting Member
10 Posts |
Posted - 2011-02-11 : 09:33:06
|
quote:
Originally posted by khtan
you can make use of a temp table with identity. Insert the records into the temp table and select back.
KH[spoiler]Time is always against us[/spoiler]
Can you give me an example of what the script would look like? Thanks! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-02-11 : 09:44:43
|
[code]create table #temp( ID int identity(1,1), col1 varchar(10), -- example col2 int, . . . .)insert into #temp (col1, col2, . .. )select <column list>from <table>order by <some col>select *from #temporder by ID[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
glilley
Starting Member
10 Posts |
Posted - 2011-02-14 : 07:38:05
|
| Thank you! |
|
|
|
|
|
|
|
|
Add 1 To Invoice# When Order# Changes