I created a sql query to calculate a running value to calculate shortage. However, I can't seem to get it to work correctly.
This is what I'm expecting. The RunningValue column is what I'm after.
However, I'm getting the following instead with my current SQL Query.
Here's my sql script. Maybe I'm not doing something right.
SELECT J.START_DATE, J.JOB_ID, J.ITEM_ID, J.REQ_QTY, J.ON_HAND,
SELECT MAX(J2.ON_HAND) - SUM(J2.REQ_QTY)
FROM JOB_ORDER J2
WHERE J2.ITEM_ID = J.ITEM_ID
AND J2.START_DATE <= J.START_DATE
AND J2.JOB_ID <= J.JOB_ID
FROM JOB_ORDER J
WHERE J.ITEM_ID = 'GDM001'
AND J.START_DATE BETWEEN '2014-11-7' AND '2014-11-14'