Distribute quantity over items

Question

I'm trying to make an oracle select to distribute a quantity over order items making the first item to have enough quantity to reach its limits and the remaining quantity would be distributed among other items.

I have the following scenario:

ORDER_ IT PRODUCT_CODE     QTY_ORDER  QTY_STOCK
------ -- --------------- ---------- ----------
682300 02 PA.000472-01            50         52
682300 03 PA.000472-01            50         52

And I would like to reach this:

ORDER_ IT PRODUCT_CODE     QTY_ORDER  QTY_STOCK
------ -- --------------- ---------- ----------
682300 02 PA.000472-01            50         50
682300 03 PA.000472-01            50         2

I don't know even how to search for this on google.

Answer 1

You can use the ROW_NUMBER analytical function for it.

Try this:

SELECT ORDER_, IT, PRODUCT_CODE, QTY_ORDER,
CASE WHEN QTY_STOCK - ((RN-1)*QTY_ORDER)  > QTY_ORDER 
THEN QTY_ORDER
ELSE QTY_STOCK - ((RN-1)*QTY_ORDER) 
END AS QTY_ORDER FROM
(Select ORDER_, IT, PRODUCT_CODE, QTY_ORDER, QTY_STOCK,
ROW_NUMBER() OVER (PARTITION BY ORDER_ ORDER BY IT) AS RN
FROM YOUR_TABLE);

Cheers!!