CODEWITHSUNDEEP
mysqljoin
meder omuraliev
meder omuraliev

Reputation: 186582

Combine temporary table of dates with two tables and fill in missing values?

I have a rates table which holds rows of nightly rates per day. I have a ratecodes table which houses different ratecodes mapped to rates.

My goal is to find any missing rates for any days for an X period of time. For this example let's use 1 month.

Desired result: 64 rows of which 2 rows are filled with information with the first rate code. The second rate code has absolutely no rows in rates but I need to show that it's actually missing dates. ( 64 because 1 month from now returns 32 days x 2 rate codes )

Two tables in question:

CREATE TABLE `ratecode` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ratecode` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


INSERT INTO `ratecode` VALUES ('1', 'BLAH');
INSERT INTO `ratecode` VALUES ('2', 'NAH');


CREATE TABLE `rates` (
  `thedate` date DEFAULT NULL,
  `rate` double DEFAULT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ratecode` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


INSERT INTO `rates` VALUES ('2014-12-27', '999', '1', '1');
INSERT INTO `rates` VALUES ('2014-12-26', '99', '2', '1');

So using this query, in 2 parts. Part 1 is a temporary table of dates from today to 1 month ahead:

CREATE TEMPORARY TABLE IF NOT EXISTS `myDates` AS (

SELECT 

CAST((SYSDATE()+INTERVAL (H+T+U) DAY) AS date) d

FROM ( SELECT 0 H
    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
  ) H CROSS JOIN ( SELECT 0 T
    UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
    UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
    UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
  ) T CROSS JOIN ( SELECT 0 U
    UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
    UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
    UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
  ) U

WHERE
  (SYSDATE()+INTERVAL (H+T+U) DAY) <= (SYSDATE()+INTERVAL 1 MONTH)

ORDER BY d ASC

);

And part 2 is the actual selection going on:

SELECT
*

FROM
rates


RIGHT JOIN myDates ON ( myDates.d = rates.thedate )
LEFT OUTER JOIN ratecode ON ( rates.ratecode = ratecode.id )

This returns only 32 rows back because in rates, there are 2 records for the first entry in ratecode. I don't get back the 32 missing rows for the other ratecode. How can I adjust in order to retain this information?

After I get the 64 rows back, I also need to filter for which ones are "blank" or haven't been entered in rates. So missing values only.

Upvotes: 0

Views: 145

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

If I understand correctly, you want to generate all the rows using a cross join, then left join to the data and filter out all th ematches:

select rc.ratecode, d.d as missingdate
from ratecode rc cross join
     mydates d left join
     rates r
     on rc.id = r.ratecode and d.d = r.thedate
where r.id is null;

Upvotes: 1

Related Questions