1. Write a query that finds all orders where the salesperson did not have an active training on the order date. The following query seems to almost work, but pulls in order id 4 which does not meet criteria. I believe there should be 5 results select * from orders t1 join training t2 on t1.salesperson_id = t2.salesperson_id where (order_date not between start_date and end_date or order_date between start_date and end_date is null) group by id; CREATE TABLE `training` ( `countID` smallint(6) NOT NULL AUTO_INCREMENT, `salesperson_id` varchar(30) DEFAULT NULL, `start_date` date DEFAULT NULL, `end_date` date DEFAULT NULL, PRIMARY KEY (`countID`)
SQL
Sales people must be trained to make orders. For a sales person to receive credit for an order, the order_date must fall between a training start and end date for that sales person.
1. Write a query that finds all orders where the salesperson did not have an active training on the order date.
The following query seems to almost work, but pulls in order id 4 which does not meet criteria.
I believe there should be 5 results
select *
from orders t1
join training t2
on t1.salesperson_id = t2.salesperson_id
where (order_date not between start_date and end_date
or order_date between start_date and end_date is null)
group by id;
CREATE TABLE `training` (
`countID` smallint(6) NOT NULL AUTO_INCREMENT,
`salesperson_id` varchar(30) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
PRIMARY KEY (`countID`)
);
INSERT INTO `training` VALUES (1,'1','2012-01-01','2012-12-31'),(2,'1','2013-01-01',NULL),(3,'2','2012-01-01','2012-07-31'),(4,'2','2013-01-01','2013-05-01'),(5,'3','2012-01-01',NULL),(6,'4','2012-01-01','2012-12-31'),(7,'5','2013-01-01',NULL),(8,'6','2012-01-01',NULL);
CREATE TABLE `orders` (
`ID` smallint(6) NOT NULL AUTO_INCREMENT,
`order_date` varchar(30) DEFAULT NULL,
`salesperson_id` varchar(30) DEFAULT NULL,
`Amount` varchar(30) DEFAULT NULL,
PRIMARY KEY (`ID`)
);
INSERT INTO `orders` VALUES (1,'2012/08/02','2','540'),(2,'2012/1/30','4','1800'),(3,'2012/07/14','1','460'),(4,'2012/01/29','2','1100'),(5,'2013/02/03','6','600'),(6,'2013/03/02','4','1250'),(7,'2012/05/06','4','150'),(8,'2013/4/01','1','1000'),(9,'2013/03/05','2','950');
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
