Jump to content

Calculating Product Offer


sam123

Recommended Posts

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

Link to comment
Share on other sites

  • Replies 55
  • Created
  • Last Reply

Top Posters In This Topic

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.

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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 */;

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 |
+-----+---------------+--------+---------------------+-------+--------+----------------+

Link to comment
Share on other sites

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 by sam123
Link to comment
Share on other sites

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 |
+-----+---------------+--------+------------------+-------+--------+----------------+

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by sam123
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.