We help IT Professionals succeed at work.

another t-sql question

ptran2000
ptran2000 asked
on
283 Views
Last Modified: 2010-03-19
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
Comment
Watch Question

Commented:
Because you want to do row-by-row processing, you will have to use a cursor.
CERTIFIED EXPERT
Top Expert 2010

Commented:
Hello ptran2000,

You need to define what you want more specifically.

Regards,

Patrick

Author

Commented:
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.
CERTIFIED EXPERT

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

Author

Commented:
Zberteoc: please provide sample code
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.