Running Balance Report

[RJL1]

I'm trying to get a report for our outbound shipments. I like to be a ble to run the report and based on shipment and requested ship date get a running total of each item in the shpments to see how far my stock will take as far as filling orders.

So for example if I have 100 Cs if blue widgets and I have 3 orders one for 80 one for 15 and one for 10 then I know I can fill the first order for 80 and have 20 left over which would let me fill the second order for 15 and have 5 left which would mean I would be 5 short to fill the third order.

SELECT
SD.SHIPMENT_ID,
SD.ITEM,
SD.REQUESTED_QTY,
SD.QUANTITY_UM AS 'UOM',
SUM(LI.ON_HAND_QTY) AS 'ON_HAND_QTY'
FROM
SHIPMENT_DETAIL SD WITH(NOLOCK)
  LEFT OUTER JOIN LOCATION_INVENTORY LI WITH(NOLOCK)
    ON LI.ITEM = SD.ITEM
WHERE
SD.SHIPMENT_ID IN ('377630-40','378811-40','78813-40')
AND SD.TOTAL_QTY > 0
GROUP BY
SD.SHIPMENT_ID,
SD.ITEM,
SD.REQUESTED_QTY,
SD.QUANTITY_UM
ORDER BY
SD.SHIPMENT_ID,
SD.ITEM

This return the following data

SHIPMENT_ID	ITEM         REQUESTED_QTY   UOM  ON_HAND_QTY
-----------	-----------  --------------  ---  -----------
377630-40	Q39C100X       100             CS   120
377630-40	QQ6522BL       80              CS   40
377630-40	QX9822ZZ       20              CS   20

377630-50	Q39C100X       50              CS   120	
377630-50	QQ6522BL       30              CS   80
377630-50	QX9822ZZ       10              CS   20

I would liek to find a way to get a running total per item but grouped by shipment like this

SHIPMENT_ID  ITEM        REQUESTED_QTY   UOM  ON_HAND_QTY  BALANCE
-----------  ----------- --------------  ---  -----------  -------
377630-40    Q39C100X    100             CS   120          20
377630-40    QQ6522BL    80              CS   40           40
377630-40    QX9822ZZ    20              CS   20           0

377630-50    Q39C100X    50              CS   20           -30	
377630-50    QQ6522BL    45              CS   40           -5	
377630-50    QX9822ZZ    10              CS   0            -10

Any help with this appreciated
Thanks
RJL

 

[markros]

Take a look at this Phil Factor competition and adjust solutions for your case:

http://ask.sqlservercentral.com/questions/826/the-fifo-stock-inventory-sql-problem

PluralSight Learning Library