Calculating Product Offer

[sam123]

Yes, i swapped the low and high prices and it worked for panasonic. But it is not working if more that one product of same type with different price. Please see the attachement for sample data. Thanks for your help. sample_data_v1.txt

[Muddy_Funster]

@sam,

The prodID in prod and whishlist tables are different.

watchlist should get the prodID from pp_prod and every entry in the product table should have a unique prodID, that's why it was made as a primary key. If the features are different between products then the product is different and it goes in as a different item. You need to have unique identifiers for each record in order to maintain and query the data successfully. wishlist should use the prodID to refference the product from prod, If you duplicate the prodID in prod then bad things will happen.

 

@barand, Liking the query you posted, It looks to me that you are using the ARP (which I ment to be Actual Retail Price) rather than calculating the discount price, is that right?

[sam123]

The problem what i am facing is that , the user is saying i am ready to pay this much amount for one product. May be that time there will not be any product added by the retailer. If after sometime the retailer added the 2 products with different price. eg. Samsung 30 LED and Samsung 40 HD and he is setting the offer if 3 customers request product of this price range he will give it for offerprice.

 

So how do we do that..? Here i got confused. Can you please advice..?

[Barand]

@Sam123,

Does this fit the requirement?

SELECT DISTINCT u.uid, u.e_mail, cnt.mfid, cnt.prodid,
   cnt.name, cnt.total, cnt.arp, cnt.discount_price
FROM
pp_watchlist w
INNER JOIN pp_user u USING (uid)
INNER JOIN
   (
   SELECT p.mfid, p.prodid, p.ARP, p.name, COUNT(w.uid) as total,
    (SELECT MIN(discount_as_price)
    FROM pp_offers
    WHERE prodID = p.prodID AND no_required <= COUNT(w.uid)
    ) as discount_price
   FROM pp_watchlist w
   INNER JOIN pp_prod p ON w.prodid = p.mfid
   WHERE p.ARP BETWEEN w.priceLow AND w.priceHigh
   GROUP BY p.prodID
   ) as cnt ON w.prodID = cnt.mfid
ORDER BY mfid, uid;

 

@Muddy

I use ARP to see if it is within the low-high range in watchlist - as required AFAIK

Also I am not checking the three types of discount (as your CASE did). Trying to keep it simple as only the one type is being used.

[Muddy_Funster]

yeah, thought that was it Barand, and keeping it simple is never a bad thing. I just thought that it was the discount price, not the ARP, that had to hit within the price watch ranges. To be honest this cURL problem I'm having is pickling my brain :'( So'm i'm gonna take a back seat for a while.

[sam123]

Thank you so much Barand. It worked.