Tag: SQL Tricks

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:


SELECT DENSE_RANK() OVER (PARTITION BY OrdNbr ORDER BY OrdNbr, LineRef) * 10  AS LineIndex
, OrdNbr
, LineRef
, InvtID
, QtyOrd
FROM   SOLine
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.

sql

 

Advertisements