[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