[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

another t-sql question

Posted on 2007-10-03
8
Medium Priority
?
268 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
0
Comment
Question by:ptran2000
8 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 20010039
Because you want to do row-by-row processing, you will have to use a cursor.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20010096
Hello ptran2000,

You need to define what you want more specifically.

Regards,

Patrick
0
 

Author Comment

by:ptran2000
ID: 20010144
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 27

Expert Comment

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

Author Comment

by:ptran2000
ID: 20010172
Zberteoc: please provide sample code
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20010260
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
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1000 total points
ID: 20010278
One small rectification:
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 AND avail<0 -- (!!!)

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

(!!!): You need to change your @negative_value only when you find the next negative avail.
0
 

Author Comment

by:ptran2000
ID: 20014129
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question