>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
What is your SQL Server version? SQL Server 2012 provides nice ways to calculate running total. Prior to SQL Server 2012 you can use cursor solution or CLR function. Check these references
MSDN thread with many helpful linksLightning Fast Hybrid RUNNING TOTAL - Can you slow it down?
If it's not broken, fix it until it is.
My Blog