troubleshooting Question

MySQL Query Help

Avatar of bdhtechnology
bdhtechnology asked on
MySQL ServerSQL
9 Comments1 Solution103 ViewsLast Modified:
I have the following query that is working, however it does not return a b.service_category value when the SUM is 0:
SELECT a.`site_id`, b.service_category 
, SUM( IF(tran_date BETWEEN '2018-01-01' AND '2018-01-31', 1, 0)) AS `interval1`
, SUM( IF(tran_date BETWEEN '2018-02-01' AND '2018-02-28', 1, 0)) AS `interval2`
, SUM( IF(tran_date BETWEEN '2018-03-01' AND '2018-03-31', 1, 0)) AS `interval3`
, SUM( IF(tran_date BETWEEN '2018-04-01' AND '2018-04-30', 1, 0)) AS `interval4`
, SUM( IF(tran_date BETWEEN '2018-05-01' AND '2018-05-31', 1, 0)) AS `interval5`
, SUM( IF(tran_date BETWEEN '2018-06-01' AND '2018-06-30', 1, 0)) AS `interval6`
, SUM( IF(tran_date BETWEEN '2018-07-01' AND '2018-07-31', 1, 0)) AS `interval7`
, SUM( IF(tran_date BETWEEN '2018-08-01' AND '2018-08-31', 1, 0)) AS `interval8`
, SUM( IF(tran_date BETWEEN '2018-09-01' AND '2018-09-30', 1, 0)) AS `interval9`
, SUM( IF(tran_date BETWEEN '2018-10-01' AND '2018-10-31', 1, 0)) AS `interval10`
, SUM( IF(tran_date BETWEEN '2018-11-01' AND '2018-11-30', 1, 0)) AS `interval11`
, SUM( IF(tran_date BETWEEN '2018-12-01' AND '2018-12-31', 1, 0)) AS `interval12`
FROM transaction_data a, code_map b 
WHERE a.site_id = 6 
AND tran_date BETWEEN '2018-01-01' AND '2018-12-31' 
AND impacts = 'P' AND type = 'S' AND status = 'A' 
AND a.service_code = b.service_code 
AND a.site_id = b.site_id 
GROUP BY a.`site_id`, b.service_category 
ORDER BY a.`site_id`, b.service_category ;

I have been attempting to modify the query so it runs well and prints 0 for values.  The query below will return all of the service_category values, however it is not correctly computing the interval values:
SELECT *
FROM code_map b 
LEFT JOIN (
    SELECT COUNT(*) AS `trans_ttl`, service_code
	, SUM( IF(tran_date BETWEEN '2018-01-01' AND '2018-01-31', 1, 0)) AS `interval1`
	, SUM( IF(tran_date BETWEEN '2018-02-01' AND '2018-02-28', 1, 0)) AS `interval2`
	, SUM( IF(tran_date BETWEEN '2018-03-01' AND '2018-03-31', 1, 0)) AS `interval3`
	, SUM( IF(tran_date BETWEEN '2018-04-01' AND '2018-04-30', 1, 0)) AS `interval4`
	, SUM( IF(tran_date BETWEEN '2018-05-01' AND '2018-05-31', 1, 0)) AS `interval5`
	, SUM( IF(tran_date BETWEEN '2018-06-01' AND '2018-06-30', 1, 0)) AS `interval6`
	, SUM( IF(tran_date BETWEEN '2018-07-01' AND '2018-07-31', 1, 0)) AS `interval7`
	, SUM( IF(tran_date BETWEEN '2018-08-01' AND '2018-08-31', 1, 0)) AS `interval8`
	, SUM( IF(tran_date BETWEEN '2018-09-01' AND '2018-09-30', 1, 0)) AS `interval9`
	, SUM( IF(tran_date BETWEEN '2018-10-01' AND '2018-10-31', 1, 0)) AS `interval10`
	, SUM( IF(tran_date BETWEEN '2018-11-01' AND '2018-11-30', 1, 0)) AS `interval11`
	, SUM( IF(tran_date BETWEEN '2018-12-01' AND '2018-12-31', 1, 0)) AS `interval12`
    FROM transaction_data
    WHERE site_id = 6 
    AND tran_date BETWEEN '2018-01-01' AND '2018-12-31'
    AND impacts = 'P' AND type = 'S' AND status = 'A' 
    GROUP BY service_code
) a ON b.service_code=a.service_code
WHERE b.site_id = 6 
GROUP BY site_id, service_category 
ORDER BY site_id, service_category ;

code_map structure:
CREATE TABLE `code_map` (
  `id` int(5) NOT NULL COMMENT 'row_id',
  `client_id` int(5) NOT NULL,
  `site_id` int(10) NOT NULL,
  `service_category` varchar(30) NOT NULL COMMENT 'service category',
  `service_code` varchar(30) NOT NULL COMMENT 'service_code'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

transaction_data structure:
CREATE TABLE `transaction_data` (
  `site_id` int(5) NOT NULL COMMENT 'site id number',
  `tran_num` int(10) NOT NULL COMMENT 'local transaction number',
  `status` varchar(30) NOT NULL,
  `fee` decimal(8,2) NOT NULL,
  `amount` decimal(8,2) NOT NULL COMMENT 'dollar amount of transaction',
  `service_code` varchar(20) NOT NULL COMMENT 'service code from ppm',
  `tran_date` datetime NOT NULL,
  `impacts` varchar(30) NOT NULL,
  `type` varchar(30) NOT NULL,
  `provider_id` varchar(9) DEFAULT NULL,
  `provider` varchar(100) NOT NULL,
  `provider_position` varchar(50) NOT NULL,
  `pay_type` varchar(45) NOT NULL,
  `patient_id` varchar(40) DEFAULT NULL,
  `adjustment_type` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY HASH (site_id)
PARTITIONS 20;
I am sure I am doing something wrong but I have been stuck on this query for over a day now and no closer to solving the issue!  Any help would be appreciated.  I will work on sanitizing some data to post as well.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros