asked on

another t-sql question

If I have a table1:

ID      Avail
1      -5
2      2
3      5
4      5

I need a query that will return this.

ID      Avail
1      -5
2      0
3      2
4      5

basically, the -5 consumes 2 from line 2 leaving 0 and then consumes 3 from line 3. leaving 2. is this possible without a cursor?

thanks
p

ptjcb

Because you want to do row-by-row processing, you will have to use a cursor.

Patrick Matthews

Hello ptran2000,

You need to define what you want more specifically.

Regards,

Patrick

ptran2000

ASKER

basically, if row 1 is negative, take it from row 2. If row 2 doesn't have enough, take it from the next row and so on.

Zberteoc

You don't have to use a cursor, a WHILE loop is prefferable and easy to control because of the id column.

ptran2000

ASKER

Zberteoc: please provide sample code

Zberteoc

DECLARE
      @id int,
      @rows int,
      @negative_value int

-- find the first id to have a negative avail
SELECT TOP 1
      @id=min(id)
FROM
      YourTable
WHERE
      avail<0

-- getting the total number of rows
SELECT
      @rows=count(*)
FROM
      YourTable

WHILE @id <= @rows
BEGIN
      SELECT @negative_value=avail FROM YourTable WHERE id=@id

--      <your_code_here>

      --increment your the counter
      SELECT
            @id=@id+1
END

ASKER CERTIFIED SOLUTION

Zberteoc

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ptran2000

ASKER

I figured out that if I implemented a running total as so I could get the desired result

http://www.databasejournal.com/features/mssql/article.php/3112381