Receiving %s by Diff Case Mgr Fields, MySQL, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

DROP TABLE `cases`;

CREATE TABLE `cases` (
  `case_id` int(10) unsigned NOT NULL,
  `case_mgr` varchar(45) DEFAULT NULL,
  `case_mgr_first` varchar(45) DEFAULT NULL,
  `who_last_called` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `item_received_date` datetime DEFAULT NULL,
  PRIMARY KEY (`case_id`),
  KEY `index_cases_case_mgr` (`case_mgr`)
);

INSERT INTO `cases`
(`case_id`, `case_mgr`, `case_mgr_first`, `who_last_called`, `created_date`, `item_received_date`)
VALUES
(101, 'wilma', 'wilma', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(102, 'bamm-bamm', 'wilma', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(103, 'fred', 'fred', NULL, '2017-05-01 08:00:00', NULL),
(104, 'fred', 'fred', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(105, 'fred', NULL, NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(106, 'fred', 'betty', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(107, 'barney', 'barney', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(108, 'barney', 'wilma', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(109, 'barney', 'wilma', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(110, 'barney', NULL, NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(111, 'wilma', NULL, NULL, '2017-05-01 08:00:00', NULL),
(112, 'betty', 'betty', NULL, '2017-05-01 08:00:00', '2017-05-01 08:00:00'),
(113, 'wilma', 'betty', NULL, '2017-05-01 08:00:00', '2017-05-01 08:00:00'),
(114, 'betty', 'betty', NULL, '2017-05-01 08:00:00', '2017-05-01 08:00:00'),
(115, 'betty', 'betty', NULL, '2017-05-01 08:00:00', NULL),
(116, 'betty', 'betty', NULL, '2017-05-01 08:00:00', NULL),
(117, 'bamm-bamm', 'fred', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(118, 'bamm-bamm', 'barney', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(119, 'fred', 'barney', NULL, '2017-05-01 08:00:00', NULL),
(120, 'wilma', 'wilma', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(121, 'bamm-bamm', 'barney', NULL, '2017-05-01 08:00:00', NULL),
(122, 'bamm-bamm', NULL, NULL, '2017-05-01 08:00:00', NULL),
(123, 'bamm-bamm', NULL, NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(124, 'bamm-bamm', 'wilma', NULL, '2017-05-01 08:00:00', NULL),
(125, 'bamm-bamm', 'wilma', NULL, '2017-05-01 08:00:00', NULL),
(126, 'wilma', 'wilma', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(127, 'wilma', 'wilma', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(128, 'wilma', 'wilma', NULL, '2017-05-01 08:00:00', NULL),
(129, 'wilma', 'barney', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(130, 'wilma', 'wilma', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(131, 'wilma', 'wilma', NULL, '2017-05-01 08:00:00', NULL),
(132, 'bamm-bamm', 'betty', NULL, '2017-05-01 08:00:00', NULL),
(133, 'bamm-bamm', 'betty', NULL, '2017-05-01 08:00:00', '2017-05-02 08:00:00'),
(134, 'wilma', 'wilma', NULL, '2017-05-01 08:00:00', NULL),
(135, 'barney', 'barney', NULL, '2017-05-01 08:00:00', NULL),
(136, 'barney', 'barney', NULL, '2017-05-01 08:00:00', NULL),
(137, 'fred', 'fred', NULL, '2017-05-01 08:00:00', NULL),
(138, 'fred', 'wilma', NULL, '2017-05-01 08:00:00', NULL),
(139, 'fred', 'fred', NULL, '2017-05-01 08:00:00', NULL),
(140, 'betty', 'betty', NULL, '2017-05-01 08:00:00', NULL)
;

SELECT 
c.case_mgr AS case_mgr, 
COUNT(*) AS count_new, 
SUM(CASE WHEN c.item_received_date IS NOT NULL THEN 1 ELSE 0 END) AS count_recd, 
SUM(CASE WHEN c.item_received_date IS NOT NULL THEN 1 ELSE 0 END)*100/COUNT(*) AS percent_recd
FROM cases c 
WHERE (c.created_date >= '2017-05-01 00:00:00' AND c.created_date <= '2017-05-31 23:59:59')   
GROUP BY c.case_mgr 
ORDER BY c.case_mgr ASC;
	
SELECT 
c.case_mgr_first AS case_mgr_first, 
COUNT(*) AS count_new, 
SUM(CASE WHEN c.item_received_date IS NOT NULL THEN 1 ELSE 0 END) AS count_recd, 
SUM(CASE WHEN c.item_received_date IS NOT NULL THEN 1 ELSE 0 END)*100/COUNT(*) AS percent_recd
FROM cases c 
WHERE (c.created_date >= '2017-05-01 00:00:00' AND c.created_date <= '2017-05-31 23:59:59') 
GROUP BY c.case_mgr_first 
ORDER BY c.case_mgr_first ASC;


absolute service time: 0,42 sec