Post reply

calculate field

  • 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

  • November 29, 2005 at 5:43 pm

    #606596

    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

  • November 30, 2005 at 6:18 am

    #606688

    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.

  • November 30, 2005 at 11:44 am

    #606831

    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...

  • November 30, 2005 at 2:25 pm

    #606880

    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

  • November 30, 2005 at 4:13 pm

    #606918

    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

  • November 30, 2005 at 4:37 pm

    #606926

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

    Thanks,

    Minh

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

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