Calculating Product Offer

sam123 · November 21, 2012

I have attached test data. Please check it.Retailer set the price of samsung as $110. If 5 customers select samsung for the price range with in the $110, i have to set the product price to $100 and i need to send this offer to those customers.This is what i am expecting to do.

Thanks for your time and help.

testdata.txt

Muddy_Funster · November 21, 2012

When I try to open that it apears to be empty...

sam123 · November 21, 2012

I have attached it again .Please chekc it.testdata.txt

Muddy_Funster · November 21, 2012

yeah, I got it this time, and.....oh crap, I thought pp_product was a unique id for each product, not a catagory. I don't think I can make the query work with the tables as they are :'( It also looks like the size and type columns in the products table have been mixed up, deffinition column also wasn't what I thought it was. Dude, I don't want to put you down or anything, but this is some bad table design.

I'll going to take a look and see if I can come up with anything that could work now I can see what data is being held in these, how many other things are running on these tables? and do you have any other tables related to this project?

I don't know if Barand or anyone else has some ideas to make this work, but I'm going to need a while on this to get anywhere.

sam123 · November 21, 2012

Thanks Muddy. These two tables are not related to any other tables.

Barand · November 21, 2012

I've a query that seems to be OK with test tables that I created (test data definition - that data for which the code works).

I'll load your data now and hope it still works.

Barand · November 21, 2012

Had to change to allow for 0 in the cust_number fields (eg Sony)

RESULTS

+-----+---------+-------+--------+
| uid | pp_name | total | price  |
+-----+---------+-------+--------+
|   1 | Samsung |	 1 | 110.00 |
|   1 | Sony    |	 5 |  82.00 |
|   1 | Sony    |	 5 |  82.00 |
|   1 | Sony    |	 5 |  82.00 |
|   1 | Sony    |	 5 |  82.00 |
|   1 | Sony    |	 5 |  82.00 |
+-----+---------+-------+--------+

This assumes UID is the id of the user to be emailed. Use DISTINCT if you don't want those duplicates. I left them in to highlight that this is a case with 1 user with many quotes for an item.

QUERY

SELECT q.uid , q.pp_name, c.total,
   CASE WHEN p.pd_deal_cust_number3 <= c.total AND p.pd_deal_cust_number3 THEN pd_deal_price3
   ELSE
    CASE WHEN p.pd_deal_cust_number2 <= c.total AND p.pd_deal_cust_number2 THEN pd_deal_price2
    ELSE
	    CASE WHEN p.pd_deal_cust_number1 <= c.total AND p.pd_deal_cust_number1 THEN pd_deal_price1
	    ELSE
	    p.pd_price
	    END
    END
   END as price
FROM pp_quote q
INNER JOIN pp_product p ON p.pd_name = q.pp_name
INNER JOIN (
   SELECT p.pd_name, COUNT(q.uid) as total
   FROM pp_product p
   INNER JOIN pp_quote q ON p.pd_name = q.pp_name
    WHERE p.pd_price BETWEEN q.pp_low AND q.pp_high
    GROUP BY p.pd_name
   ) as c ON p.pd_name = c.pd_name
WHERE p.pd_price BETWEEN q.pp_low AND q.pp_high;

Muddy_Funster · November 21, 2012

OK, I got a bit carried away with re-designing the tables :sweat:

Fortunately Barand was on the case and you don't need them now, but here's what I wound up with anyway (just incase anyone thought I was bunking off :tease-03: )

...I wanted to just attach the dump as a text file, but for some reason my firefox is throwing a wobbly with attaching files, so here it is in a code block, sorry 'bout this

-- MySQL dump 10.13 Distrib 5.5.8, for Win32 (x86)
--
-- Host: localhost Database: sandbox
-- ------------------------------------------------------
-- Server version 5.5.8

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `pp_catagorys`
--

DROP TABLE IF EXISTS `pp_catagorys`;
/*!40101 SET @saved_cs_client	 = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pp_catagorys` (
`catID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`catName` varchar(254) NOT NULL,
`parentID` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`catID`),
UNIQUE KEY `name` (`catName`,`parentID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pp_catagorys`
--

LOCK TABLES `pp_catagorys` WRITE;
/*!40000 ALTER TABLE `pp_catagorys` DISABLE KEYS */;
INSERT INTO `pp_catagorys` VALUES (4,'3D TV',1),(2,'High Deffinition',1),(3,'Standard Deffinition',1),(1,'TV',0);
/*!40000 ALTER TABLE `pp_catagorys` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pp_manufacturer`
--

DROP TABLE IF EXISTS `pp_manufacturer`;
/*!40101 SET @saved_cs_client	 = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pp_manufacturer` (
`manftID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(254) NOT NULL,
`blurb` text,
`weblink` varchar(254) DEFAULT NULL,
`assetLink` varchar(254) DEFAULT NULL,
PRIMARY KEY (`manftID`),
UNIQUE KEY `name_IDX` (`name`),
FULLTEXT KEY `blurb_IDX` (`blurb`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pp_manufacturer`
--

LOCK TABLES `pp_manufacturer` WRITE;
/*!40000 ALTER TABLE `pp_manufacturer` DISABLE KEYS */;
INSERT INTO `pp_manufacturer` VALUES (1,'Samsung',NULL,NULL,NULL),(2,'Sony',NULL,NULL,NULL),(3,'Panasonic',NULL,NULL,NULL),(4,'Onida',NULL,NULL,NULL),(5,'Test',NULL,NULL,NULL);
/*!40000 ALTER TABLE `pp_manufacturer` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pp_offers`
--

DROP TABLE IF EXISTS `pp_offers`;
/*!40101 SET @saved_cs_client	 = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pp_offers` (
`prodID` int(11) NOT NULL,
`no_required` int(11) NOT NULL,
`discount_as_percent` tinyint(3) DEFAULT NULL,
`discount_as_value` decimal(11,2) DEFAULT NULL,
`discount_as_price` decimal(11,2) DEFAULT NULL,
`offer_ends` date DEFAULT NULL,
`offer_status` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`prodID`,`no_required`,`offer_status`),
KEY `per_IDX` (`discount_as_percent`),
KEY `val_IDX` (`discount_as_value`),
KEY `price_IDX` (`discount_as_price`),
KEY `end_IDX` (`offer_ends`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pp_offers`
--

LOCK TABLES `pp_offers` WRITE;
/*!40000 ALTER TABLE `pp_offers` DISABLE KEYS */;
INSERT INTO `pp_offers` VALUES (1,3,NULL,NULL,105.00,NULL,0),(1,5,NULL,NULL,100.00,NULL,0),(1,10,NULL,NULL,95.00,NULL,0),(2,5,NULL,NULL,82.00,NULL,0),(2,10,NULL,NULL,85.00,NULL,0);
/*!40000 ALTER TABLE `pp_offers` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pp_prod`
--

DROP TABLE IF EXISTS `pp_prod`;
/*!40101 SET @saved_cs_client	 = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pp_prod` (
`prodID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`manftID` int(11) DEFAULT NULL,
`catagory` int(11) NOT NULL,
`sub_cat` int(11) DEFAULT NULL,
`type` int(11) NOT NULL,
`sub_type` int(11) DEFAULT NULL,
`name` varchar(254) NOT NULL,
`modelNo` varchar(254) DEFAULT NULL,
`RRP` decimal(11,2) DEFAULT NULL,
`ARP` decimal(11,2) DEFAULT NULL,
`inStock` int(9) NOT NULL DEFAULT '0',
`img` varchar(254) DEFAULT NULL,
`tmbnail` varchar(254) DEFAULT NULL,
`techSpec` text,
`specialInfo` text,
`warrantyInfo` text,
`descript` text,
PRIMARY KEY (`prodID`),
UNIQUE KEY `name_IDX` (`name`) USING HASH,
UNIQUE KEY `model_IDX` (`modelNo`) USING HASH,
KEY `man_IDX` (`manftID`),
KEY `arp_IDX` (`ARP`),
FULLTEXT KEY `desc_IDX` (`descript`),
FULLTEXT KEY `spec_IDX` (`techSpec`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pp_prod`
--

LOCK TABLES `pp_prod` WRITE;
/*!40000 ALTER TABLE `pp_prod` DISABLE KEYS */;
INSERT INTO `pp_prod` VALUES (1,1,1,3,1,10,'Samsung 30 LED','S30SDTV',110.00,110.00,12,'images/uploads/samsung1.jpg','images/uploads/resized_samsung1.jpg','Test','Test','TEst','Very good.'),(2,2,1,2,4,11,'Sony 36 inch Plasma','Sy36HDTV',90.00,90.00,10,'images/uploads/samsung.jpg','images/uploads/resized_samsung.jpg','Test','Test','Test','Test');
/*!40000 ALTER TABLE `pp_prod` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pp_product`
--

DROP TABLE IF EXISTS `pp_product`;
/*!40101 SET @saved_cs_client	 = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pp_product` (
`pd_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`pd_rname` varchar(200) NOT NULL,
`pd_product` varchar(200) NOT NULL,
`pd_name` varchar(100) NOT NULL DEFAULT '',
`pd_description` text NOT NULL,
`pd_type` varchar(200) DEFAULT NULL,
`pd_size` varchar(200) DEFAULT NULL,
`pd_defenition` varchar(200) NOT NULL,
`pd_price` decimal(7,2) NOT NULL DEFAULT '0.00',
`pd_deal_price1` decimal(7,2) DEFAULT NULL,
`pd_deal_price2` decimal(7,2) DEFAULT NULL,
`pd_deal_price3` decimal(7,2) DEFAULT NULL,
`pd_deal_cust_number1` int(11) DEFAULT NULL,
`pd_deal_cust_number2` int(11) DEFAULT NULL,
`pd_deal_cust_number3` int(11) DEFAULT NULL,
`pd_qty` smallint(5) unsigned NOT NULL DEFAULT '0',
`pd_image` varchar(200) DEFAULT NULL,
`pd_thumbnail` varchar(200) DEFAULT NULL,
`pd_retailer_logo` varchar(200) DEFAULT NULL,
`pd_technical_specs` text,
`pd_product_specs` text,
`pd_warranty` text,
`pd_pickup` text,
`pd_special_details` text,
`pd_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pd_last_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`pd_id`),
KEY `pd_name` (`pd_name`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pp_product`
--

LOCK TABLES `pp_product` WRITE;
/*!40000 ALTER TABLE `pp_product` DISABLE KEYS */;
INSERT INTO `pp_product` VALUES (1,2,'Test','TV','Samsung','Very good.','30','LED','Standard',110.00,105.00,100.00,95.00,3,5,10,12,'images/uploads/samsung1.jpg','images/uploads/resized_samsung1.jpg','','Test','Testr','TEst','Test','Test','2012-11-10 21:42:37','0000-00-00 00:00:00'),(2,2,'Test','TV','Sony','Test','36','Plasma','HD',90.00,85.00,82.00,0.00,10,5,0,10,'images/uploads/samsung.jpg','images/uploads/resized_samsung.jpg','','Test','test','test','test','test','2012-11-10 21:46:04','0000-00-00 00:00:00'),(3,2,'Test','TV','Panasonic','Tesr','40','Projection','3D',105.00,0.00,0.00,0.00,0,0,0,5,'images/uploads/samsung_tv.jpg','images/uploads/resized_samsung_tv.jpg','','wewrw','werwrw','werwrw','werwrw','werwrwr','2012-11-10 21:50:56','0000-00-00 00:00:00'),(4,7,'Test1','TV','Test','test description','44','LED','Standard',800.00,700.00,650.00,600.00,10,40,100,1,'images/uploads/Yamaha RX-V671BL Receiver.jpg','images/uploads/resized_Yamaha RX-V671BL Receiver.jpg','','Technical details','Product specifications','Warranty offers','Delivery details','Other special details','2012-11-12 19:21:34','0000-00-00 00:00:00'),(5,2,'Test','TV','Onida','Onida is a good TV','36','Plasma','HD',200.00,190.00,0.00,0.00,15,0,0,50,'images/uploads/samsung1.jpg','images/uploads/resized_samsung1.jpg','','qweq','dsfsf','asdads','asdad','sddsfsfd','2012-11-14 07:52:41','0000-00-00 00:00:00'),(6,2,'TEst','TV','Panasonic','sgrtgrht','36','Plasma','HD',100.00,95.00,90.00,85.00,5,10,15,12,'images/uploads/samsung.jpg','images/uploads/resized_samsung.jpg','','qweqwe','erere','ererer','werwe','rererte','2012-11-15 21:58:21','0000-00-00 00:00:00');
/*!40000 ALTER TABLE `pp_product` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pp_quote`
--

DROP TABLE IF EXISTS `pp_quote`;
/*!40101 SET @saved_cs_client	 = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pp_quote` (
`pp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`pp_product` varchar(200) NOT NULL,
`pp_name` varchar(100) NOT NULL DEFAULT '',
`pp_type` varchar(200) DEFAULT NULL,
`pp_size` varchar(200) DEFAULT NULL,
`pp_defenition` varchar(200) NOT NULL,
`pp_price` decimal(7,2) NOT NULL DEFAULT '0.00',
`pp_svdate` int(11) DEFAULT NULL,
`pp_range` varchar(200) DEFAULT NULL,
`pp_ramount` decimal(7,2) DEFAULT NULL,
`pp_high` decimal(7,2) NOT NULL,
`pp_low` decimal(7,2) NOT NULL,
`pp_status` varchar(60) NOT NULL,
`pp_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pp_edate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pp_last_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`pp_id`),
KEY `pp_name` (`pp_name`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pp_quote`
--

LOCK TABLES `pp_quote` WRITE;
/*!40000 ALTER TABLE `pp_quote` DISABLE KEYS */;
INSERT INTO `pp_quote` VALUES (2,1,'TV','Samsung','Plasma','36','Standard',200.00,3,'Yes',13.00,213.00,187.00,'Open','2012-11-10 18:57:53','2012-11-10 08:57:53','0000-00-00 00:00:00'),(3,1,'TV','Samsung','Plasma','36','Standard',200.00,3,'Yes',13.00,213.00,187.00,'Open','2012-11-10 18:59:09','2012-11-10 08:59:09','0000-00-00 00:00:00'),(4,1,'TV','Samsung','Plasma','36','Standard',200.00,3,'Yes',13.00,213.00,187.00,'Open','2012-11-10 18:59:51','2012-11-10 08:59:51','0000-00-00 00:00:00'),(6,1,'TV','Sony','LED','30','Standard',90.00,7,'Yes',10.00,110.00,90.00,'Open','2012-11-10 22:04:15','2012-11-17 22:04:15','0000-00-00 00:00:00'),(7,1,'TV','Sony','LED','30','Standard',100.00,7,'Yes',10.00,110.00,90.00,'Open','2012-11-10 22:06:51','2012-11-17 22:06:51','0000-00-00 00:00:00'),(8,1,'TV','Sony','LED','30','Standard',100.00,7,'Yes',10.00,110.00,90.00,'Open','2012-11-10 22:07:47','2012-11-17 22:07:47','0000-00-00 00:00:00'),(9,1,'TV','Sony','LED','30','Standard',100.00,7,'Yes',10.00,110.00,90.00,'Open','2012-11-10 22:18:07','2012-11-17 22:18:07','0000-00-00 00:00:00'),(10,1,'TV','Sony','LED','30','Standard',100.00,7,'Yes',10.00,110.00,90.00,'Open','2012-11-10 22:18:34','2012-11-17 22:18:34','0000-00-00 00:00:00'),(11,1,'TV','Samsung','LED','30','Standard',100.00,5,'Yes',10.00,110.00,90.00,'Open','2012-11-10 22:22:18','2012-11-15 22:22:18','0000-00-00 00:00:00');
/*!40000 ALTER TABLE `pp_quote` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pp_type`
--

DROP TABLE IF EXISTS `pp_type`;
/*!40101 SET @saved_cs_client	 = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pp_type` (
`typeID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`typeName` varchar(254) NOT NULL,
`parentID` int(11) NOT NULL DEFAULT '0',
`catID` int(11) DEFAULT NULL,
PRIMARY KEY (`typeID`),
UNIQUE KEY `name_IDX` (`typeName`,`parentID`,`catID`) USING BTREE,
KEY `FK_cat` (`catID`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pp_type`
--

LOCK TABLES `pp_type` WRITE;
/*!40000 ALTER TABLE `pp_type` DISABLE KEYS */;
INSERT INTO `pp_type` VALUES (1,'LED',0,3),(2,'LED',0,2),(3,'LED',0,4),(4,'Plasma',0,2),(5,'Plasma',0,3),(6,'Plasma',0,4),(7,'Projection',0,2),(8,'Projection',0,3),(9,'Projection',0,4),(10,'30 inch',1,NULL),(11,'36 inch',4,NULL),(12,'40 inch',9,NULL),(13,'44',1,NULL);
/*!40000 ALTER TABLE `pp_type` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pp_user`
--

DROP TABLE IF EXISTS `pp_user`;
/*!40101 SET @saved_cs_client	 = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pp_user` (
`uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`firstName` varchar(254) NOT NULL,
`lastName` varchar(254) DEFAULT NULL,
`e_mail` varchar(254) NOT NULL,
`pass` varchar(254) NOT NULL,
`lastLogin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`previousLogin` datetime DEFAULT NULL,
`userStatus` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`),
KEY `name_IDX` (`firstName`),
KEY `email_IDX` (`e_mail`),
KEY `pass_IDX` (`pass`),
KEY `last_IDX` (`lastLogin`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pp_user`
--

LOCK TABLES `pp_user` WRITE;
/*!40000 ALTER TABLE `pp_user` DISABLE KEYS */;
INSERT INTO `pp_user` VALUES (1,'Test','User','test@your.net','','2012-11-21 14:25:28',NULL,0);
/*!40000 ALTER TABLE `pp_user` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pp_watchlist`
--

DROP TABLE IF EXISTS `pp_watchlist`;
/*!40101 SET @saved_cs_client	 = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pp_watchlist` (
`uid` int(11) unsigned NOT NULL,
`prodID` int(11) NOT NULL,
`priceLow` decimal(11,2) NOT NULL,
`priceHigh` decimal(11,2) NOT NULL,
`expire_on` date NOT NULL,
`setStatus` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`,`prodID`,`setStatus`),
KEY `low_IDX` (`priceLow`),
KEY `high_IDX` (`priceHigh`),
KEY `expire_IDX` (`expire_on`),
KEY `stat_IDX` (`setStatus`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pp_watchlist`
--

LOCK TABLES `pp_watchlist` WRITE;
/*!40000 ALTER TABLE `pp_watchlist` DISABLE KEYS */;
INSERT INTO `pp_watchlist` VALUES (0,2,89.00,82.00,'0000-00-00',0),(1,1,187.00,213.00,'2012-11-30',0);
/*!40000 ALTER TABLE `pp_watchlist` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

sam123 · November 21, 2012

Thank you so much Barand and Muddy_Funster. I will try both the ways to solve my problem.

Muddy, i have recreated all the tables as per your suggestion and now its look like i can do the job easily. Can you please help me to get the query working from this..? I think i need to join two table pp_watchlist and pp_offer to get the product count. and set the offer. Please advise.

Barand · November 21, 2012

@Muddy_Funster,

The only place I'd differ on your revised model is the pp_prod table. You don't want the category and sub_category, just the sub_category (other item dependency).

The parent link in the category table gives category. Same goes for type and sub_type.

Muddy_Funster · November 21, 2012

Barand - yeah, I agree with you on that. I was still refining the model when I posted that up, so there is likely other flaws that will show on close inspection, I had assumed that as you had provided a solution it would just be a refference on how I thought would have been a better design.

sam123 I came up with this for the query, it's a bit clunky (I suck at using CASE) but it get's me the right results (I think! - been a long day)

SELECT pp_prod.prodID, num_watchers, no_required, ARP,
CASE
   WHEN discount_as_percent IS NOT NULL THEN(SELECT ARP-(ARP/100*discount_as_percent) as discount)
   WHEN discount_as_value IS NOT NULL THEN(SELECT ARP-(discount_as_value) as discount)
   ELSE (SELECT discount_as_price as discount)
END as discount,
firstName, lastName, e_mail
FROM pp_user
INNER JOIN
(SELECT pp_watchlist.uid, pp_watchlist.prodID, no_required, num_watchers, discount_as_percent, discount_as_value, discount_as_price

  FROM pp_watchlist  
  INNER JOIN
   (SELECT prodID, uid, count(*) AS num_watchers FROM pp_watchlist GROUP BY prodID) AS user_count
  ON (user_count.uid = pp_watchlist.uid)
  INNER JOIN pp_offers ON (pp_offers.prodID = pp_watchlist.prodID)
  )AS watcher_info
ON (watcher_info.uid = pp_user.uid)
INNER JOIN pp_prod ON (watcher_info.prodID = pp_prod.prodID)
WHERE num_watchers >= no_required
GROUP BY prodID

Muddy_Funster · November 21, 2012

yeah, that query's incomplete - I totally missed out the the bit where we exclude by price range. I'll take another shot at tomorrow when I'm back at my dev comp.

Barand · November 21, 2012

@Muddy_Funster,

I notice that you have an "expires_on" date in the watchlist table with a date of '0000-00-00' (when not defined) in your data. It makes the query logic far simpler if you use '9999-12-31' in this instance. That way you can just look for "WHERE expires_on >= CURDATE()" without also having to check for the zero date condition.

Muddy_Funster · November 22, 2012

could be a cunning plan, I only really put it in there as optional functionality for future development, same as the status fields, so wasn't realy looking at it when working out the query. I'll sleep on it and see what the morning brings...although I hate mornings.... :-\

sam123 · November 22, 2012

Thanks Muddy. I am waiting for the updated query.

Muddy_Funster · November 22, 2012

I'm not happy with it, but here you go. The best I could think of is to just wrap what I gave you in another SELECT. I'm doing this because, as I said before, I suck at CASE and as such can't think how to address the case in the where clause of the same level select. I'll not say this is optimum and wont take offence if anyone screams that it is far from it, but it should at least work. I thought about using the offerExpire as Barand suggested, but I think that as it's not part of your orriginal layout or functionality request I'll leave that be for now. Barand can probably rewrite this better but here it is anyway

SELECT prodID, num_watchers, no_required, ARP, priceLow, ROUND(discount, 2) as offerPrice, priceHigh, firstName, lastName, e_mail
FROM
(SELECT pp_prod.prodID, num_watchers, no_required, ARP, priceLow,
CASE
    WHEN discount_as_percent IS NOT NULL THEN(SELECT ARP-(ARP/100*discount_as_percent) as discount)
    WHEN discount_as_value IS NOT NULL THEN(SELECT ARP-(discount_as_value) as discount)
    ELSE (SELECT discount_as_price as discount)
END as discount,
priceHigh, firstName, lastName, e_mail
FROM pp_user
INNER JOIN
 (SELECT pp_watchlist.uid, pp_watchlist.prodID, priceLow, priceHigh, no_required, num_watchers, discount_as_percent, discount_as_value, discount_as_price
   FROM pp_watchlist  
   INNER JOIN
    (SELECT prodID, uid, count(*) AS num_watchers FROM pp_watchlist GROUP BY prodID) AS user_count
   ON (user_count.uid = pp_watchlist.uid)
   INNER JOIN pp_offers ON (pp_offers.prodID = pp_watchlist.prodID)
   )AS watcher_info
ON (watcher_info.uid = pp_user.uid)
INNER JOIN pp_prod ON (watcher_info.prodID = pp_prod.prodID)
WHERE num_watchers >= no_required
GROUP BY prodID) as pp_value_return
WHERE discount BETWEEN priceLow AND priceHigh

sam123 · November 23, 2012

Wow!! Its worked..Thanks a lot Muddy.

Barand · November 24, 2012

OFFERS

+--------+-------------+---------------------+-------------------+-------------------+------------+--------------+
| prodID | no_required | discount_as_percent | discount_as_value | discount_as_price | offer_ends | offer_status |
+--------+-------------+---------------------+-------------------+-------------------+------------+--------------+
|	 1 |		 3 |			 NULL |			 NULL |		 105.00 | NULL	 |		 0 |
|	 1 |		 5 |			 NULL |			 NULL |		 100.00 | NULL	 |		 0 |
|	 1 |		 10 |			 NULL |			 NULL |			 95.00 | NULL	 |		 0 |
|	 2 |		 2 |			 NULL |			 NULL |			 85.00 | NULL	 |		 0 |
|	 2 |		 5 |			 NULL |			 NULL |			 82.00 | NULL	 |		 0 |
+--------+-------------+---------------------+-------------------+-------------------+------------+--------------+

WATCHLIST

+-----+--------+----------+-----------+------------+-----------+
| uid | prodID | priceLow | priceHigh | expire_on | setStatus |
+-----+--------+----------+-----------+------------+-----------+
| 1 |	 1 | 100.00 | 213.00 | 2012-11-30 |		 0 |
| 1 |	 2 | 90.00 | 120.00 | 2012-12-31 |		 0 |
| 2 |	 1 | 90.00 | 110.00 | 0000-00-00 |		 0 |
| 2 |	 2 | 90.00 | 120.00 | 0000-00-00 |		 0 |
+-----+--------+----------+-----------+------------+-----------+

MY QUERY ATTEMPT

SELECT DISTINCT u.uid, u.e_mail, 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.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 USING (prodID)
WHERE p.ARP BETWEEN w.priceLow AND w.priceHigh
GROUP BY p.prodID
) as cnt
ORDER BY uid, prodID ;

RESULTS

+-----+---------------+--------+---------------------+-------+--------+----------------+
| uid | e_mail	 | prodID | name			 | total | arp | discount_price |
+-----+---------------+--------+---------------------+-------+--------+----------------+
| 1 | test@your.net |	 1 | Samsung 30 LED	 |	 2 | 110.00 | NULL |
| 1 | test@your.net |	 2 | Sony 36 inch Plasma |	 2 | 90.00 | 85.00 |
| 2 | ann@dot.com |	 1 | Samsung 30 LED	 |	 2 | 110.00 | NULL |
| 2 | ann@dot.com |	 2 | Sony 36 inch Plasma |	 2 | 90.00 | 85.00 |
+-----+---------------+--------+---------------------+-------+--------+----------------+

sam123 · November 24, 2012

Muddy I have one more problem. The prodID in prod and whishlist tables are different. The user is saying that i am ready to buy a product if it is with in a specific range with out seeing the product which retailer had already added.I.e he is ready to buy a product say samsung if the price is with in the range of $400-$700. Then retiler is adding samsung and setting its price to $500. So for differentiating the products i have added one more field mfID in both prod and offer table.

I have modified your sql and it is working but not as expected in the offer field.Rest of the fields are working fine.

SELECT prodID, mfID, num_watchers, no_required, ARP, priceLow, ROUND(discount, 2) as offerPrice, priceHigh, firstName, lastName, e_mail
FROM
(SELECT pp_prod.prodID, pp_prod.mfID, num_watchers, no_required, ARP, priceLow,
CASE
	 WHEN discount_as_percent IS NOT NULL THEN(SELECT ARP-(ARP/100*discount_as_percent) as discount)
	 WHEN discount_as_value IS NOT NULL THEN(SELECT ARP-(discount_as_value) as discount)
	 ELSE (SELECT discount_as_price as discount)
END as discount,
priceHigh, firstName, lastName, e_mail
FROM pp_user
INNER JOIN
(SELECT pp_watchlist.uid, pp_watchlist.prodID, priceLow, priceHigh, no_required, num_watchers, discount_as_percent, discount_as_value, discount_as_price
 FROM pp_watchlist
INNER JOIN
	 (SELECT prodID, uid, count(*) AS num_watchers FROM pp_watchlist	
GROUP BY prodID ) AS user_count
 ON (user_count.uid = pp_watchlist.uid)
 INNER JOIN pp_offers ON (pp_offers.mfID = pp_watchlist.prodID)
 )AS watcher_info
ON (watcher_info.uid = pp_user.uid)
INNER JOIN pp_prod ON (watcher_info.prodID = pp_prod.mfID)
WHERE num_watchers >= no_required
GROUP BY prodID) as pp_value_return
WHERE discount BETWEEN priceLow AND priceHigh

Query Result:

prodID, mfID, num_watchers, no_required, ARP , priceLow, offerPrice, priceHigh, firstName, lastName, email

1 | 1 | 3 | 3 | 550.00 | 400.00 | 540.00 | 700.00 | Test | Last | test@test.com

2 | 1 | 3 | 3 | 500.00 | 400.00 | 540.00 | 700.00 | Test | Last | test@test.com

Only in the offer filed it is giving me wrong answer. For the prodID2 the offerprice i should get 480 instead of 540.

Is there any way i can achieve this. Thanks for your help.

Edited November 24, 2012 by sam123

sam123 · November 25, 2012

Also the query is not working if i add the product panasonic and then the user number is 3, it should show the offerprice. But it is not showing any offer. Please help me. I am scratching my head here, but in vein..I am attaching sample data with this mail.

sample_data.txt

Barand · November 25, 2012

Could it be because your sample data only contains UID = 1 in the watchlist and only for products 1 and 4?

And for product 4 the low prices are higher than the high prices.

Edited November 25, 2012 by Barand

Barand · November 25, 2012

After swapping the low/high prices my query gave this

+-----+---------------+--------+------------------+-------+--------+----------------+
| uid | e_mail	    | prodID | name			 | total | arp    | discount_price |
+-----+---------------+--------+------------------+-------+--------+----------------+
|   1 | test@your.net |	  1 | Samsung 30 LED   |	 3 | 550.00 |		 540.00 |
|   1 | test@your.net |	  4 | Panasonic 36 LED |	 3 | 700.00 |		 640.00 |
+-----+---------------+--------+------------------+-------+--------+----------------+

sam123 · November 26, 2012

Thanks for your help Barand. But my code is giving me only one result. I tried your query also.The result which i am getting as follows.

+-----+---------------+--------+------------------+-------+--------+----------------+
| uid | e_mail		  | prodID | name				  | total | arp  | discount_price |
+-----+---------------+--------+------------------+-------+--------+----------------+
|   1 | test@your.net |   1 | Samsung 30 LED   |		 3 | 550.00 |		    540.00 |
+-----+---------------+--------+------------------+-------+--------+----------------+

Can you please post the code which you were trying?

The retiler can add the same product with different feature and different price.

So if any retiler add a product " Samsung 40 HD " and its price is $ 600,and if he is ready to give offer if 3 customers price quote request the product with the price range of $400-$800, the result we should get like this.

+-----+---------------+--------+------------------+-------+--------+----------------+
| uid | e_mail		  | prodID | name				  | total | arp  | discount_price |
+-----+---------------+--------+------------------+-------+--------+----------------+
|   1 | test@your.net |   1 | Samsung 30 LED   |		 3 | 550.00 |		    540.00 |
|   1 | test@your.net |   4 | Panasonic 36 LED |		 3 | 700.00 |		    640.00 |
|   1 | test@your.net |   2 | Samsung 40 HD    |		 3 | 600.00 |		    580.00 |
+-----+---------------+--------+------------------+-------+--------+----------------+

This is what i am trying to acheive here.

sam123 · November 26, 2012

I have made some modifications in my database and ran your sql and I got the following results.

+-----+---------------+--------+------------------+-------+--------+----------------+
| uid | e_mail		 | prodID | name				 | total | arp | discount_price |
+-----+---------------+--------+------------------+-------+--------+----------------+
| 1 | test@your.net | 1 | Samsung 30 LED |		 3 | 550.00 |		 540.00 |
| 1 | test@your.net | 4 | Panasonic 36 LED |		 3 | 700.00 |		 640.00 |
+-----+---------------+--------+------------------+-------+--------+----------------+

But i want to get the following result as i wrote it in the prvious reply.

+-----+---------------+--------+------------------+-------+--------+----------------+
| uid | e_mail		 | prodID | name								 | total | arp | discount_price |
+-----+---------------+--------+------------------+-------+--------+----------------+
| 1 | test@your.net | 1 | Samsung 30 LED |				 3 | 550.00 |				 540.00 |
| 1 | test@your.net | 4 | Panasonic 36 LED |				 3 | 700.00 |				 640.00 |
| 1 | test@your.net | 2 | Samsung 40 HD	 |			 3 | 600.00 |				 580.00 |
+-----+---------------+--------+------------------+-------+--------+----------------+

If i have two samsung entry in the prod table how can i get the above expected result..? Is this possible..? Thanks for your help.

Edited November 26, 2012 by sam123

Barand · November 26, 2012

Did you swap round the low and high Panasonic prices?

Can you post your revised data for the 3 tables?

Sign In

Calculating Product Offer

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information