#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    36
    Rep Power
    7

    Help please with SQL query


    hi.

    i have this table for example:


    and this SQL query:
    PHP Code:
    prcrSql "SELECT scaleqty, fld01, dim2, dim1, prcrule, finaldate FROM prcrdata WHERE finaldate>=getDate()-1 AND (dim1=30 OR dim1=40) ORDER BY dim2"

    prcrRS.Open prcrSql


    WHILE NOT prcrRS.EOF

    insPRCR 
    "INSERT INTO prcr (mtrl,ofprice,minqty,maxqty,client,prcrule,fdate) VALUES ('"&prcrRS("dim2")&"','"&prcrRS("fld01")&"','"&prcrRS("minqty")&"','"&prcrRS("maxqty")&"','"&prcrRS("dim1")&"','"&prcrRS("prcrule")&"','"&prcr("prdate")&"')"
    myCon.Execute insPRCR

    prcrRS
    .MoveNext
    WEND 
    in the table above (image table) i have only the minqty (minimum quantity) and i want to set the maxqty (maximum quantity) 1 less than minqty of the next record.
    for example at row 1: 4 (5-1)
    at row 2: 7 (8-1)
    and so on...

    i want this result:


    how i can do this with SQL or classic ASP or PHP;

    thanks in advance.
    Last edited by Kyriakos; December 20th, 2013 at 05:40 AM.
  2. #2
  3. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,111
    Rep Power
    487
    Instead of printing the results directly to screen, store them in an array first so then when it comes to displaying on screen, you can reference the next element's value.
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    36
    Rep Power
    7
    Originally Posted by badger_fruit
    Instead of printing the results directly to screen, store them in an array first so then when it comes to displaying on screen, you can reference the next element's value.
    i dont know how to do this. can you give me some code please?
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Writing the code is your job. We can help you with concrete problems, but we won't do the work for you.

    The array approach does work, but it's very inefficient and not really appropriate. Simply order the rows by the minimum quantity, loop through them, store each row in a variable and use this variable to refer to the pevious row (if present).

    As pseudo code:

    Code:
    last_minimum := null
    last_price := null
    
    each row in rows:
    	if last_minimum /= null:
    		print "Minimum: " + last_minimum + ", Maximum: " + (row.minimum - 1) + ", Price: " + last_price
    	if.
    	
    	last_minimum := row.minimum
    	last_price := row.price
    each.
    
    if last_minimum /= null and last_price /= null:
    	print "Minimum: " + last_minimum + ", Maximum: Infinity, Price: " + last_price
    if.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    36
    Rep Power
    7
    i have changed my sql to this
    PHP Code:
    prcrSql "SELECT p1.scaleqty AS minqty, isnull(MIN(p2.scaleqty)-1,'150000') AS maxqty, p1.fld01, p1.dim2, p1.dim1, p1.prcrule, p1.finaldate FROM prcrdata p1
    LEFT JOIN prcrdata p2 ON p2.scaleqty > p1.scaleqty AND p2.finaldate>=getDate()-1 AND p2.dim1=30
    WHERE p1.finaldate>=getDate()-1 AND p1.dim1=30
    GROUP BY p1.scaleqty, p1.fld01, p1.dim2, p1.dim1, p1.prcrule, p1.finaldate ORDER BY p1.dim2" 
    but i have a problem. in the first row the maxqty is always "2" and in the last row if the minqty is with one digit then is always "9", when is with two digits is "99" and with 3 digits is "999". what's going on?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,337
    Rep Power
    594
    This is really a MySQL question not a PHP question. Your p1.dim2 field is probably defined as varchar rather than int. That means the sort is alphabetic not numeric. You can fix that by CASTing p1.dim2 or simply adding 0 to it.
    PHP Code:
    prcrSql "SELECT p1.scaleqty AS minqty, isnull(MIN(p2.scaleqty)-1,'150000') AS maxqty, p1.fld01, p1.dim2, p1.dim1, p1.prcrule, p1.finaldate FROM prcrdata p1
    LEFT JOIN prcrdata p2 ON p2.scaleqty > p1.scaleqty AND p2.finaldate>=getDate()-1 AND p2.dim1=30
    WHERE p1.finaldate>=getDate()-1 AND p1.dim1=30
    GROUP BY p1.scaleqty, p1.fld01, p1.dim2, p1.dim1, p1.prcrule, p1.finaldate ORDER BY CAST (p1.dim2 AS SIGNED INTEGER)" 
    or
    PHP Code:
    prcrSql "SELECT p1.scaleqty AS minqty, isnull(MIN(p2.scaleqty)-1,'150000') AS maxqty, p1.fld01, p1.dim2, p1.dim1, p1.prcrule, p1.finaldate FROM prcrdata p1
    LEFT JOIN prcrdata p2 ON p2.scaleqty > p1.scaleqty AND p2.finaldate>=getDate()-1 AND p2.dim1=30
    WHERE p1.finaldate>=getDate()-1 AND p1.dim1=30
    GROUP BY p1.scaleqty, p1.fld01, p1.dim2, p1.dim1, p1.prcrule, p1.finaldate ORDER BY p1.dim2+0" 
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo