/**
 * This query will build up the 3 temporary tables,
 * and the runner will then query these tables.
 */
 
-- Create all the temporary tables we need...
CREATE TEMPORARY TABLE IF NOT EXISTS `nacl_crosstab_1` (
  `JOB_NO` VARCHAR(20) NOT NULL,
  `NACL_DATE` DATE,
  PRIMARY KEY (`JOB_NO`)
);
 
CREATE TEMPORARY TABLE IF NOT EXISTS `nacl_crosstab_2` (
  `JOB_NO` VARCHAR(20) NOT NULL,
  `NACL_DATE` DATE,
  PRIMARY KEY (`JOB_NO`)
);
 
CREATE TEMPORARY TABLE IF NOT EXISTS `nacl_crosstab_3` (
  `JOB_NO` VARCHAR(20) NOT NULL,
  `NACL_DATE` DATE,
  PRIMARY KEY (`JOB_NO`)
);
 
-- Ensure they're empty before we insert.
TRUNCATE TABLE `nacl_crosstab_1`;
TRUNCATE TABLE `nacl_crosstab_2`;
TRUNCATE TABLE `nacl_crosstab_3`;
 
-- Insert the data we want into these temp tables.
INSERT INTO `nacl_crosstab_1` (`JOB_NO`, `NACL_DATE`)
SELECT r.JOB_NO, MIN(l.`DATE`)
FROM repairs r
INNER JOIN nacllog l ON (l.`JOB_NO` = r.`JOB_NO`)
WHERE (l.`REASON` = 'C')
	OR (l.`REASON` = '')
	OR (l.`REASON` IS NULL)
GROUP BY r.`JOB_NO`;
 
INSERT INTO `nacl_crosstab_2` (`JOB_NO`, `NACL_DATE`)
SELECT c.JOB_NO, MIN(l.`DATE`)
FROM nacl_crosstab_1 c
INNER JOIN nacllog l ON (l.`JOB_NO` = c.`JOB_NO`)
	AND (l.`DATE` > c.`NACL_DATE`)
WHERE (l.`REASON` = 'C')
	OR (l.`REASON` = '')
	OR (l.`REASON` IS NULL)
GROUP BY c.`JOB_NO`;
 
INSERT INTO `nacl_crosstab_3` (`JOB_NO`, `NACL_DATE`)
SELECT c.JOB_NO, MIN(l.`DATE`)
FROM nacl_crosstab_2 c
INNER JOIN nacllog l ON (l.`JOB_NO` = c.`JOB_NO`)
	AND (l.`DATE` > c.`NACL_DATE`)
WHERE (l.`REASON` = 'C')
	OR (l.`REASON` = '')
	OR (l.`REASON` IS NULL)
GROUP BY
	c.`JOB_NO`;
 
-- Finally, get the results in a usable format.
SELECT
	r.`HOUSE_KEY`,
	TRIM(h.`HOUSENO`) AS `HOUSENO`,
	h.`ROAD`,
	h.`DISTRICT`,
	h.`POSTCODE`,
	r.`JOB_NO`,
	n1.`NACL_DATE` AS `NACL_1`,
	n2.`NACL_DATE` AS `NACL_2`,
	n3.`NACL_DATE` AS `NACL_3`
FROM
	repairs r
LEFT OUTER JOIN
	house h ON (h.`HOUSE_KEY` = r.`HOUSE_KEY`)
LEFT OUTER JOIN
  `nacl_crosstab_1` n1 ON (n1.`JOB_NO` = r.`JOB_NO`)
LEFT OUTER JOIN
  `nacl_crosstab_2` n2 ON (n2.`JOB_NO` = r.`JOB_NO`)
LEFT OUTER JOIN
  `nacl_crosstab_3` n3 ON (n3.`JOB_NO` = r.`JOB_NO`);
WHERE
	(r.COMP_DATE BETWEEN [startdate] AND [stopdate])
	AND (r.`CALL_TYPE` = [calltype])