SQL Query Recalculating Running Totals

I'm taking a set of transactions and amounts, and I want to create a new amount column, with the following logic --

1. Check a running total of (new) amounts thus far.
2. If adding this amount to the previous total would bring the total to less than zero, the new amount field should be zero. Otherwise, it should be equal to the old amount.

Here's an example of what I'm looking for --

Item    Record    Old amount  New Amount    Running Total
1       1         100         100           100
1       2        -100        -100             0
1       3        -200           0             0
1       4         500         500           500
1       5        -300        -300           200
1       6         300         300           500

My running total starts at zero.

My first amount is 100, and that doesn't take the total < 0, so I pass it through and set the new amount to 100.

My second amount is -100, and that doesn't take my running total of 100 to < 0, so I set the new amount to -100.

My third amount is -200. That would take the running total of 0 to -200, < 0. Thus, I set the new amount to 0.

My fourth amount is 500. It gets passed through.

My fifth amount is -300. That would take the running total of 500 to 200, which is still >= 0. It gets passed through.

My sixth amount is 300. It gets passed through, leaving me with a final amount total of 500.

The difficult part is in cases like record five here. In order to know that it won't take the final running total below zero, you need to have already calculated the new total for record 3.

I think you can do this by setting up common table expressions in order to make a recursive query, but I've foun开发者_JS百科dered on how exactly to create that. If possible, I'd like to avoid cursors.

this is a WINDOW FUNCTION solution with a wrapping CASE statement.

look up LAG