[thelist] query help (ms sql 2000)

Brian Cummiskey brian at hondaswap.com
Fri Jan 12 06:40:02 CST 2007


I'm working on a query that displays discount pricing based on multiple 
quantities ordered.

ie, buy 1, $19.99
buy 2 or 3, $15.99 each
buy 4 or more, $14.99 each


The table that these prices are stored in is an absolute disaster from a 
3rd party that I have no control over.


The pricing is stored in 9 qty fields and 9 price fields per row based 
on item number:

itemno, qty1, qty2, qty3, qty4, qty5, qty6, qty7, qty8, qty9, pri1, 
pri2, pri3, pri4, pri5, pri6, pri7, pri8, pri9

in the above example, the data would look like this:

item123, 1, 2, 3, 9999999, 0, 0, 0, 0, 0, 1999, 1599, 1599, 1499, 0, 0, 
0,0, 0

the 9999999 denotes the last pricing group price...  "or more"


another example:

buy 1-3, $19.99
buy 4-5, $15.99 each
buy 6 or more, $14.99 each

item456, 1, 2, 3, 4, 5, 9999999, 0, 0, 0, 1999, 1999, 1999, 1599, 1599, 
1499, 0, 0, 0




The current application shows each line item and is done in a loop 
through the scripting language (classic asp)
ie:

buy 1, $19.99
buy 2, $19.99
buy 3, $19.99
buy 4, $15.99
buy 5, $15.99
buy 6 or more, $14.99

<%
Ssuccess=getmultiprice(itemno)
' Main function call that gets the recordset, and returns the below var's

    response.write "<table>" & vbcrlf
            response.write "<tr><th>Special Volume Discount!</th></tr>" 
& vbcrlf
                mCount = 1
                for mCount = 1 to itemcount
                    sMpbQty = qty(mcount)
                    sMpbPrice = trim(price(mcount))
                    if sMpbQty > 9999 then
                        sMpbQty = sMpbqty2
                    end if
                    if sMpbqty > 0 then
                        if sMpbQty = sMpbqty2 then
                            sOrMore = " or more"
                        end if
                        response.write "<tr><td>Buy " & sMpbQty & 
sOrMore& ", pay $" & sMpbPrice & " each.</td></tr>"
                        sMpbqty2 = sMpbqty + 1
                    else
                        exit for
                    end if
                next
            Response.write "</table>"
%>

I am at a loss for trying to come up with a solution that groups these 
prices.  Ideally, I would like a pure sql solution without resorting to 
scripting a loop as it is currently.

I'm thinking temporary table formatted in a way that makes it useful for 
grouping, but I'm still not sure which way to attack this.

Appreciate the advice as always.




More information about the thelist mailing list