Create your own incrementing scheme in SQL for Dynamics SL Order Line Items
Earlier today, I was asked if I could come up with a query that would allow you to bring back line items for a sales order. But create a numbering scheme for the line items that re-start for each new order number. So, example:
You can see below, that ORD1234 has 3 items, indexed 1,2,3. and ORD5555 has 2 line items indexed 1,2
I accomplished this by using the DENSE_RANK().
Here is sample code:
SELECT DENSE_RANK() OVER (PARTITION BY OrdNbr ORDER BY OrdNbr, LineRef) * 10 AS LineIndex
ORDER BY OrdNbr, LineIndex
What I wanted to do in this was to show the order number, lineRef, invtID, and qtyOrd. But I wanted to show the LineIndex in increments of 10 that restarted for every time the OrdNbr changed. I have one of the orders that have multiple line items circled below.