Hello all,

I have a situation in my database where orders are made for a given reference number and there can be many orders for a given ref number. In addition to this, shipments can be made against these orders but only the reference number is provided so as to decrement what in total remains to be shipped for the ref number. My question is how can I calculate the remaining amount left on the line order in my result set. The following shows the result set I have and what I want to do is calculate the remaining amount for each order:

Ref #	  OrderDate        QtyOrdered	   TotalShippedRef #	        RefRemain	          Line Remain
1001	    2/1/2013	   200	                          350		        1050
1001	    2/2/2013	   300	                          350		        1050
1001	    2/4/2013	   400	                          350		        1050
1001	    2/6/2013	   500	                          350		        1050

This shows that for ref number 1001 I have shipped a total of 350 and since the sum of the order amounts is 1400 what remains for the ref number to be shipped is 1050.

I want to calculate the line remain amount so that the first line says 0 (200 - 350 = 0 with 150 left), the second line would be 150 (300 - 150 left leaving zero left for the rest), the third line would be 400 and the last line would be 500.

Do I need to use a cursor to go through the result set or some kind of recursive update?

Any help is appreciated.

Bob