How can I assign Serial Numbers in multiple ranges
-
I know this is not the best database practice, however, it is a business requirement.
Our orders are assigned order #s in ranges based on what product they are for, let's say products A-E, i.e.
Product A: Order #s 000000-099999
Product B: Order #s 100000-199999
Product C: Order #s 200000-299999
Product D: Order #s 300000-399999
Prodcut E: Order #s 400000-499999
I want to store all of these orders in one Orders table because they're all...orders. How can I assign these unique order #s in these different ranges?
I am using an Access 2003 Data Project using SQL Server 2000 as the backend and am fluent in VBA and SQL, I just need to be pointed in the right direction.
The other piece to this, which isn't as big a concern is that orders get entered in lump sums, so say a customer makes an order for 50 shipments, I would like to be able to assign them 50 order#s based on the next 50 consecutive #s in that range. If I can do it all at once, that would be great, but one at a time works for now, as long as they are consecutive, but by product.
I think that is clear, but I can elaborate if it will help anyone help me.
Thank you!!!
-
An insert trigger can handle it.
-
Oh, great, so, when it creates a new record it sets the order_no to Max(order_no BETWEEN @MinProduct and @MaxProduct) + 1
Where @MinProduct is:
Case Product
Case A
000000
Case B
100000
Case C
200000
Case D
300000
Case E
400000
End
And @MaxProduct is a similar thing.
I'll try it out, thank you.
(I know the syntax on all of the above isn't correct, but I'll figure it out)
-
Just an FYI, you might have already considered this, but make sure to code your trigger for multiple inserts. I've been burnt by the reality of insert statements that fire the trigger ONCE for multiple inserts, your inserted table is actually many rows of inserted records so you'll have to either write a script that takes that into account or use cursors(Ugh) if you can't.
-
Thank you for the tip, it's little stuff like that that causes hours of frustration.
I think my worst example of that is when I use <> XXX criteria in queries, and forget that i have to add an OR IS NULL if I want the other 10,000 rows to show up
.OR actually, I think this one was worse. When doing LEFT OUTER joins but realizing that they were being joined to stuff and tHEN I Was eliminating it using a <> criteria. Instead I had to put the <> criteria in the FROM Clause
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply