ah, so your product_min represents the lower min level of quantity...
then you can do this:
1) add a row for product_min = 1 to your table, so that you have
absolute min level and price for it defined (even if it is the same
price as min level 5, you still have to add this row or the query will
Is this just a list of price breaks where the single item price is in a
different table?
--
William E. Seiter
-Original Message-
From: Mike Little [mailto:m...@nzsolutions.co.nz]
Sent: Wednesday, May 05, 2010 8:49 PM
To: cf-talk
Subject: Re: query for experts
azadi (thanks for everyones help by the way)...
SELECT pricing.*
FROM pricing PCP
WHERE product_min >= 6
ORDER BY product_min, (product_min/6)
LIMIT 1;
does not work as it outputs the price for where a min qty of 10 applies. it
should output the price where the min is 5.
i am thinking of going
try this (reposted from my other answer, just in case you missed it):
SELECT TOP 1 product_min, product_price
FROM tblPricing
WHERE product_min >= 8
ORDER BY product_min, (product_min/8)
Azadi
On 06/05/2010 11:49, Mike Little wrote:
> william that would be ideal. my problem is that the clien
hmm... just tested my query in slq 2005 express and sure enough it did
not work!
this seems to do the trick, though (include min_qty column in ORDER BY
clause):
SELECT TOP 1 min_qty, price
FROM ...
WHERE min_qty >= 8
ORDER BY min_qty, (min_qty/8) ASC
Azadi
On 06/05/2010 10:36, Mike Little wro
william that would be ideal. my problem is that the client inputs their own
price breaks. most of their products require a minimum greater than 1.
azadi, i have done the following and it still outputs some odd results (using a
cart qty of 4 to test)...
SELECT FLOOR(product_price * CEILING(4 /
just to clarify:
i am assuming your price is applicable to min_qty or less, i.e.
5 : $12.00 = price is $12 for 5 or less items
10 : $11.55 = price is $11.55 for 6-10 items
am i wrong in my assumption? (maybe that's why you said my query did not
work...)
Azadi
On 06/05/2010 11:29, Azadi Saryev
On 06/05/2010 10:36, Mike Little wrote:
> azadi - your query works for a qty of 4 but not anything between 5 and 10
that's strange...
this works fine for me in MySQL:
SELECT price
FROM ...
WHERE min_qty >= 8
ORDER BY (min_qty/8)
LIMIT 1
the only difference to MS SQL is using TOP 1 instead of LI
co.nz]
Sent: Wednesday, May 05, 2010 10:36 PM
To: cf-talk
Subject: Re: query for experts?
azadi - your query works for a qty of 4 but not anything between 5 and 10.
rick - not possible as qty's range massively per product type
andy - cannot get this query to work, outputs wrong price
maureen - w
I think I am a bit confused as to what your scenario is...
Is 5 the minimum a customer can purchase?
If they order 5 or more, is $12 their price, or if they order 1-5 it is $12?
If the pricing is for 1-5 = $12 each, then I think you can adjust your table
a little to get the proper results.
Add
M
To: cf-talk
Subject: Re: query for experts?
azadi - your query works for a qty of 4 but not anything between 5 and 10.
rick - not possible as qty's range massively per product type
andy - cannot get this query to work, outputs wrong price
azadi - your query works for a qty of 4 but not anything between 5 and 10.
rick - not possible as qty's range massively per product type
andy - cannot get this query to work, outputs wrong price
maureen - was just an example
~|
If your field name is min_qty why are you using product_min in your query?
SELECT price
FROM tblPricing
WHERE min_qty <= cart.qty
ORDER BY min_qty DESC
LIMIT 1;
On Wed, May 5, 2010 at 5:11 PM, Mike Little wrote:
>
> hey guys, hopefully an easy one...
>
> tblPricing
> --
> mi
If the minimum quantity is 5 for an item, then you should consider not
letting them place only 4 in their cart. If you're still wanting to move in
that direction then a query like this should do the trick:
SELECT FLOOR(price * CEILING(cart.qty/ min_qty) ) AS price
FROM tblPricing
WHERE id = 123
use a dropdown for the quantity selection with your
min_qty values instead of letting the user input any number
they want...
-Original Message-
From: Mike Little [mailto:m...@nzsolutions.co.nz]
Sent: Wednesday, May 05, 2010 8:12 PM
To: cf-talk
Subject: query for experts?
hey guys, hop
something like this?
SELECT TOP 1 price
FROM tblPricing
WHERE min_qty > #somevalue#
ORDER BY (min_qty/#somevalue#)
Azadi
On 06/05/2010 08:11, Mike Little wrote:
> hey guys, hopefully an easy one...
>
> tblPricing
> --
> min_qty (INT)
> price
>
> a product has the following
16 matches
Mail list logo
