SQL to update table with "item numbers"
Posted on 2011-09-13
I am trying to duplicate some functionality I had in an Access app in a SQL Server environment via asp.net web forms.
My problem is with numbering items that are assigned to a Purchase Order. After creating a new Purchase Order, users are directed to a screen where they can add items to their "cart". The cart is based on a temporary table created via stored procedure with a parameter for the table name, which is the username plus date plus "TempItems".
When they click a "submit" button, I want to sort the items they've added in the temp table and populate an "itemnumber" field with a sequential number - for each record, it's the count of items with itemdescriptions that are <= each record's itemdescription, if that makes any sense.
I used DCount in my Access app, and the Append Query I ran had a field with alias "Sequence" which was: Sequence: Dcount("itemid", "tempitems", "itemdescription <=itemdescription). This method worked very well.
How do I do it in SQL Server??