calculate field

Question

Post reply

calculate field

joemai

Hall of Fame

Points: 3496
More actions
November 29, 2005 at 5:30 pm

#111539

Hi All,
Please rescue me. I have a table like this
Product_Number     Qty_low
A1                       3
A1                       6
A1                       12
A1                       18

how can I write a query so that I can calculate my qty_high field? The qty_high will be the next qty_low -1:
Product_Number     Qty_low    QTY_high
A1                       3              5
A1                       6              11
A1                       12             17
A1                       18             999999999
Thanks for any ideas
Minh Vu

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Please provide DDL and sample data. See http://www.aspfaq.com/etiquette.asp?id=5006 for instructions.

Here is an untested solution:

select MyTable.Product_Number

, MyTable.Qty_low

, COALESCE( MIN (H.Qty_low) - 1 , 999999999) from MyTable

Left outer join

MyTable as H

on H.Product_Number = MyTable.Product_Number

and H.Qty_low > MyTable.Qty_low

group by MyTable.Product_Number

, MyTable.Qty_low

SQL = Scarcely Qualifies as a Language

bledu SSChampion Points: 12335 More actions · Answer 2

joemai i can't understand what it is you are requesting.

please put provide ddl and sample data as carl has said

Everything you can imagine is real.

Tom Erwine Mr or Mrs. 500 Points: 516 More actions · Answer 3

joemai,

what is your ordering? Is it by qty_low? Seems that this is some sort of audit trail/snapshot showing the qty at each snapshot. What if the next entry into the table is :

A1 7

Does this become the last row or does it become the second row? Maybe an explanation of what the results mean would help...

joemai Hall of Fame Points: 3496 More actions · Answer 4

thank you so much for all of the help. Based on Carl's input, I used self-join to work it out and it worked for me. All I did is adding an automatical int field and used self join.

select product_number, qty_low,

qty_high = (select isnull(min(convert(int,a.qty_low) -1),'99999')

from #test a where a.product_number = b.product_number and a.idx >b.idx),

price, price/convert(int,qty_low) as newprice

from #test b

order by product_number, qty_low

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Is there some reason that you are using non-standard SQL such as ISNULL instead of COALESCE and CONVERT instead of CAST ?

The SQL originally posted will run unchanged under DB2, Oracle 9, Oracle 10, MS SQL Server 2000, Sybase and mySQL 4.x.

The SQL that you wrote will run against MS SQL Server and maybe Sybase.

SQL = Scarcely Qualifies as a Language

joemai Hall of Fame Points: 3496 More actions · Answer 6

No special reason at all, since I get used to isnull and convert function already.

Thanks,

Minh