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
| OrdNbr | InvtID | LineIndex |
| ORD1234 | IN82344 | 1 |
| ORD1234 | IN82341 | 2 |
| ORD1234 | IN82344 | 3 |
| ORD5555 | IN089723 | 1 |
| ORD5555 | IN0123998 | 2 |
I accomplished this by using the DENSE_RANK().
Here is sample code:
[code language=”sql”]
SELECT DENSE_RANK() OVER (PARTITION BY OrdNbr ORDER BY OrdNbr, LineRef) * 10 AS LineIndex
, OrdNbr
, LineRef
, InvtID
, QtyOrd
FROM SOLine
ORDER BY OrdNbr, LineIndex
[/code]
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.
