DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PopulateDailyAirQual`()
COMMENT 'Procedure to populate daily air quality '
BEGIN
DECLARE exit handler for SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
END;
SET time_zone="+10:00";
-- determine the most recent data already inserted into table
SET @lastUpdated = (select DATE(LogDate) FROM DailyAirQual ORDER BY LogDate DESC LIMIT 1);
-- values from realtime table
DROP TEMPORARY TABLE IF EXISTS rtData;
CREATE TEMPORARY TABLE IF NOT EXISTS rtData (
LogDateRT DATE NOT NULL,
OutPm1_avgRT DECIMAL (5,1) NULL,
OutPm1_minRT DECIMAL (5,1) NULL,
OutTPm1_minRT TIME NULL,
OutPm1_maxRT DECIMAL (5,1) NULL,
OutTPm1_maxRT TIME NULL,
OutPm2p5_avgRT DECIMAL (5,1) NULL,
OutPm2p5_minRT DECIMAL (5,1) NULL,
OutTPm2p5_minRT TIME NULL,
OutPm2p5_maxRT DECIMAL (5,1) NULL,
OutTPm2p5_maxRT TIME NULL,
OutPm2p5_1hr_avgRT DECIMAL (5,1) NULL,
OutPm2p5_1hr_minRT DECIMAL (5,1) NULL,
OutTpm2p5_1hr_minRT TIME NULL,
OutPm2p5_1hr_maxRT DECIMAL (5,1) NULL,
OutTpm2p5_1hr_maxRT TIME NULL,
OutPm2p5_3hr_avgRT DECIMAL (5,1) NULL,
OutPm2p5_3hr_minRT DECIMAL (5,1) NULL,
OutTpm2p5_3hr_minRT TIME NULL,
OutPm2p5_3hr_maxRT DECIMAL (5,1) NULL,
OutTpm2p5_3hr_maxRT TIME NULL,
OutPm2p5_24hr_avgRT DECIMAL (5,1) NULL,
OutPm2p5_24hr_minRT DECIMAL (5,1) NULL,
OutTpm2p5_24hr_minRT TIME NULL,
OutPm2p5_24hr_maxRT DECIMAL (5,1) NULL,
OutTpm2p5_24hr_maxRT TIME NULL,
OutPm10_avgRT DECIMAL (5,1) NULL,
OutPm10_minRT DECIMAL (5,1) NULL,
OutTPm10_minRT TIME NULL,
OutPm10_maxRT DECIMAL (5,1) NULL,
OutTPm10_maxRT TIME NULL,
OutPm10_1hr_avgRT DECIMAL (5,1) NULL,
OutPm10_1hr_minRT DECIMAL (5,1) NULL,
OutTpm10_1hr_minRT TIME NULL,
OutPm10_1hr_maxRT DECIMAL (5,1) NULL,
OutTpm10_1hr_maxRT TIME NULL,
OutPm10_3hr_avgRT DECIMAL (5,1) NULL,
OutPm10_3hr_minRT DECIMAL (5,1) NULL,
OutTpm10_3hr_minRT TIME NULL,
OutPm10_3hr_maxRT DECIMAL (5,1) NULL,
OutTpm10_3hr_maxRT TIME NULL,
OutPm10_24hr_avgRT DECIMAL (5,1) NULL,
OutPm10_24hr_minRT DECIMAL (5,1) NULL,
OutTpm10_24hr_minRT TIME NULL,
OutPm10_24hr_maxRT DECIMAL (5,1) NULL,
OutTpm10_24hr_maxRT TIME NULL,
OutAqiPm2p5_avgRT DECIMAL (5,1) NULL,
OutAqiPm2p5_minRT DECIMAL (5,1) NULL,
OutTAqiPm2p5_minRT TIME NULL,
OutAqiPm2p5_maxRT DECIMAL (5,1) NULL,
OutTAqiPm2p5_maxRT TIME NULL,
OutAqiPm2p5_1hr_avgRT DECIMAL (5,1) NULL,
OutAqiPm2p5_1hr_minRT DECIMAL (5,1) NULL,
OutTAqiPm2p5_1hr_minRT TIME NULL,
OutAqiPm2p5_1hr_maxRT DECIMAL (5,1) NULL,
OutTAqiPm2p5_1hr_maxRT TIME NULL,
OutAqiPm2p5_3hr_avgRT DECIMAL (5,1) NULL,
OutAqiPm2p5_3hr_minRT DECIMAL (5,1) NULL,
OutTAqiPm2p5_3hr_minRT TIME NULL,
OutAqiPm2p5_3hr_maxRT DECIMAL (5,1) NULL,
OutTAqiPm2p5_3hr_maxRT TIME NULL,
OutAqiPm2p5_24hr_avgRT DECIMAL (5,1) NULL,
OutAqiPm2p5_24hr_minRT DECIMAL (5,1) NULL,
OutTAqiPm2p5_24hr_minRT TIME NULL,
OutAqiPm2p5_24hr_maxRT DECIMAL (5,1) NULL,
OutTAqiPm2p5_24hr_maxRT TIME NULL,
OutAqiPm10_avgRT DECIMAL (5,1) NULL,
OutAqiPm10_minRT DECIMAL (5,1) NULL,
OutTAqiPm10_minRT TIME NULL,
OutAqiPm10_maxRT DECIMAL (5,1) NULL,
OutTAqiPm10_maxRT TIME NULL,
OutAqiPm10_1hr_avgRT DECIMAL (5,1) NULL,
OutAqiPm10_1hr_minRT DECIMAL (5,1) NULL,
OutTAqiPm10_1hr_minRT TIME NULL,
OutAqiPm10_1hr_maxRT DECIMAL (5,1) NULL,
OutTAqiPm10_1hr_maxRT TIME NULL,
OutAqiPm10_3hr_avgRT DECIMAL (5,1) NULL,
OutAqiPm10_3hr_minRT DECIMAL (5,1) NULL,
OutTAqiPm10_3hr_minRT TIME NULL,
OutAqiPm10_3hr_maxRT DECIMAL (5,1) NULL,
OutTAqiPm10_3hr_maxRT TIME NULL,
OutAqiPm10_24hr_avgRT DECIMAL (5,1) NULL,
OutAqiPm10_24hr_minRT DECIMAL (5,1) NULL,
OutTAqiPm10_24hr_minRT TIME NULL,
OutAqiPm10_24hr_maxRT DECIMAL (5,1) NULL,
OutTAqiPm10_24hr_maxRT TIME NULL
) AS (
SELECT DATE(Realtime.LogDateTime) AS LogDateRT,
ag.OutPm1_avgRT AS OutPm1_avgRT,
ROUND(MIN(a1.pm1),1) AS OutPm1_minRT, a1.OutTPm1_minRT,
ROUND(MAX(b1.pm1),1) AS OutPm1_maxRT, b1.OutTPm1_maxRT,
ag.OutPm2p5_avgRT AS OutPm2p5_avgRT,
ROUND(MIN(c1.pm2p5),1) AS OutPm2p5_minRT, c1.OutTPm2p5_minRT,
ROUND(MAX(d1.pm2p5),1) AS OutPm2p5_maxRT, d1.OutTPm2p5_maxRT,
ag.OutPm2p5_1hr_avgRT AS OutPm2p5_1hr_avgRT,
ROUND(MIN(e1.pm2p5_1hr),1) AS OutPm2p5_1hr_minRT, e1.OutTPm2p5_1hr_minRT,
ROUND(MAX(f1.pm2p5_1hr),1) AS OutPm2p5_1hr_maxRT, f1.OutTpm2p5_1hr_maxRT,
ag.OutPm2p5_3hr_avgRT AS OutPm2p5_3hr_avgRT,
ROUND(MIN(g1.pm2p5_3hr),1) AS OutPm2p5_3hr_minRT, g1.OutTPm2p5_3hr_minRT,
ROUND(MAX(h1.pm2p5_3hr),1) AS OutPm2p5_3hr_maxRT, h1.OutTPm2p5_3hr_maxRT,
ag.OutPm2p5_24hr_avgRT AS OutPm2p5_24hr_avgRT,
ROUND(MIN(i1.pm2p5_24hr),1) AS OutPm2p5_24hr_minRT, i1.OutTPm2p5_24hr_minRT,
ROUND(MAX(j1.pm2p5_24hr),1) AS OutPm2p5_24hr_maxRT, j1.OutTPm2p5_24hr_maxRT,
ag.OutPm10_avgRT AS OutPm10_avgRT,
ROUND(MIN(k1.pm10),1) AS OutPm10_minRT, k1.OutTPm10_minRT,
ROUND(MAX(l1.pm10),1) AS OutPm10_maxRT, l1.OutTPm10_maxRT,
ag.OutPm10_1hr_avgRT AS OutPm10_1hr_avgRT,
ROUND(MIN(m1.pm10_1hr),1) AS OutPm10_1hr_minRT, m1.OutTPm10_1hr_minRT,
ROUND(MAX(n1.pm10_1hr),1) AS OutPm10_1hr_maxRT, n1.OutTPm10_1hr_maxRT,
ag.OutPm10_3hr_avgRT AS OutPm10_3hr_avgRT,
ROUND(MIN(o1.pm10_3hr),1) AS OutPm10_3hr_minRT, o1.OutTPm10_3hr_minRT,
ROUND(MAX(p1.pm10_3hr),1) AS OutPm10_3hr_maxRT, p1.OutTPm10_3hr_maxRT,
ag.OutPm10_24hr_avgRT AS OutPm10_24hr_avgRT,
ROUND(MIN(q1.pm10_24hr),1) AS OutPm10_24hr_minRT, q1.OutTPm10_24hr_minRT,
ROUND(MAX(r1.pm10_24hr),1) AS OutPm10_24hr_maxRT, r1.OutTPm10_24hr_maxRT,
ag.OutAqiPm2p5_avgRT AS OutAqiPm2p5_avgRT,
ROUND(MIN(a2.AQIpm2p5),1) AS OutAqiPm2p5_minRT, a2.OutTAqiPm2p5_minRT,
ROUND(MAX(b2.AQIpm2p5),1) AS OutAqiPm2p5_maxRT, b2.OutTAqiPm2p5_maxRT,
ag.OutAqiPm2p5_1hr_avgRT AS OutAqiPm2p5_1hr_avgRT,
ROUND(MIN(c2.AQIpm2p5_1hr),1) AS OutAqiPm2p5_1hr_minRT, c2.OutTAqiPm2p5_1hr_minRT,
ROUND(MAX(d2.AQIpm2p5_1hr),1) AS OutAqiPm2p5_1hr_maxRT, d2.OutTAqiPm2p5_1hr_maxRT,
ag.OutAqiPm2p5_3hr_avgRT AS OutAqiPm2p5_3hr_avgRT,
ROUND(MIN(e2.AQIpm2p5_3hr),1) AS OutAqiPm2p5_3hr_minRT, e2.OutTAqiPm2p5_3hr_minRT,
ROUND(MAX(f2.AQIpm2p5_3hr),1) AS OutAqiPm2p5_3hr_maxRT, f2.OutTAqiPm2p5_3hr_maxRT,
ag.OutAqiPm2p5_24hr_avgRT AS OutAqiPm2p5_24hr_avgRT,
ROUND(MIN(g2.AQIpm2p5_24hr),1) AS OutAqiPm2p5_24hr_minRT, g2.OutTAqiPm2p5_24hr_minRT,
ROUND(MAX(h2.AQIpm2p5_24hr),1) AS OutAqiPm2p5_24hr_maxRT, h2.OutTAqiPm2p5_24hr_maxRT,
ag.OutAqiPm10_avgRT AS OutAqiPm10_avgRT,
ROUND(MIN(i2.AQIpm10),1) AS OutAqiPm10_minRT, i2.OutTAqiPm10_minRT,
ROUND(MAX(j2.AQIpm10),1) AS OutAqiPm10_maxRT, j2.OutTAqiPm10_maxRT,
ag.OutAqiPm10_1hr_avgRT AS OutAqiPm10_1hr_avgRT,
ROUND(MIN(k2.AQIpm10_1hr),1) AS OutAqiPm10_1hr_minRT, k2.OutTAqiPm10_1hr_minRT,
ROUND(MAX(l2.AQIpm10_1hr),1) AS OutAqiPm10_1hr_maxRT, l2.OutTAqiPm10_1hr_maxRT,
ag.OutAqiPm10_3hr_avgRT AS OutAqiPm10_3hr_avgRT,
ROUND(MIN(m2.AQIpm10_3hr),1) AS OutAqiPm10_3hr_minRT, m2.OutTAqiPm10_3hr_minRT,
ROUND(MAX(n2.AQIpm10_3hr),1) AS OutAqiPm10_3hr_maxRT, n2.OutTAqiPm10_3hr_maxRT,
ag.OutAqiPm10_24hr_avgRT AS OutAqiPm10_24hr_avgRT,
ROUND(MIN(o2.AQIpm10_24hr),1) AS OutAqiPm10_24hr_minRT, o2.OutTAqiPm10_24hr_minRT,
ROUND(MAX(p2.AQIpm10_24hr),1) AS OutAqiPm10_24hr_maxRT, p2.OutTAqiPm10_24hr_maxRT
FROM Realtime
INNER JOIN(
-- average values
SELECT Realtime.LogDateTime AS LogDate,
ROUND(AVG(Realtime.pm1),1) AS OutPm1_avgRT,
ROUND(AVG(Realtime.pm2p5),1) AS OutPm2p5_avgRT,
ROUND(AVG(Realtime.pm2p5_1hr),1) AS OutPm2p5_1hr_avgRT,
ROUND(AVG(Realtime.pm2p5_3hr),1) AS OutPm2p5_3hr_avgRT,
ROUND(AVG(Realtime.pm2p5_24hr),1) AS OutPm2p5_24hr_avgRT,
ROUND(AVG(Realtime.pm10),1) AS OutPm10_avgRT,
ROUND(AVG(Realtime.pm10_1hr),1) AS OutPm10_1hr_avgRT,
ROUND(AVG(Realtime.pm10_3hr),1) AS OutPm10_3hr_avgRT,
ROUND(AVG(Realtime.pm10_24hr),1) AS OutPm10_24hr_avgRT,
ROUND(AVG(Realtime.AQIpm2p5),1) AS OutAqiPm2p5_avgRT,
ROUND(AVG(Realtime.AQIpm2p5_1hr),1) AS OutAqiPm2p5_1hr_avgRT,
ROUND(AVG(Realtime.AQIpm2p5_3hr),1) AS OutAqiPm2p5_3hr_avgRT,
ROUND(AVG(Realtime.AQIpm2p5_24hr),1) AS OutAqiPm2p5_24hr_avgRT,
ROUND(AVG(Realtime.AQIpm10),1) AS OutAqiPm10_avgRT,
ROUND(AVG(Realtime.AQIpm10_1hr),1) AS OutAqiPm10_1hr_avgRT,
ROUND(AVG(Realtime.AQIpm10_3hr),1) AS OutAqiPm10_3hr_avgRT,
ROUND(AVG(Realtime.AQIpm10_24hr),1) AS OutAqiPm10_24hr_avgRT
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
GROUP BY DAY(Realtime.LogDateTime), MONTH(Realtime.LogDateTime), YEAR(Realtime.LogDateTime)
) ag ON DATE(Realtime.LogDateTime) = DATE(ag.LogDate)
-- a1: Pm1_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm1_minRT, pm1
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm1 ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
a1 ON (Realtime.LogDateTime) = (a1.LogDate)
-- b1: Pm1_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm1_maxRT, pm1
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm1 DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
b1 ON DATE(Realtime.LogDateTime) = DATE(b1.LogDate)
-- c1: Pm2p5_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm2p5_minRT, pm2p5
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5 ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
c1 ON DATE(Realtime.LogDateTime) = DATE(c1.LogDate)
-- d1: Pm2p5_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm2p5_maxRT, pm2p5
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5 DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
d1 ON DATE(Realtime.LogDateTime) = DATE(d1.LogDate)
-- e1: Pm2p5_1hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_1hr_minRT, pm2p5_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_1hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
e1 ON DATE(Realtime.LogDateTime) = DATE(e1.LogDate)
-- f1: Pm2p5_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_1hr_maxRT, pm2p5_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_1hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
f1 ON DATE(Realtime.LogDateTime) = DATE(f1.LogDate)
-- g1: Pm2p5_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_3hr_minRT, pm2p5_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_3hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
g1 ON DATE(Realtime.LogDateTime) = DATE(g1.LogDate)
-- h1: Pm2p5_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_3hr_maxRT, pm2p5_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_3hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
h1 ON DATE(Realtime.LogDateTime) = DATE(h1.LogDate)
-- i1: Pm2p5_24hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_24hr_minRT, pm2p5_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_24hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
i1 ON DATE(Realtime.LogDateTime) = DATE(i1.LogDate)
-- j1: Pm2p5_24hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_24hr_maxRT, pm2p5_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_24hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
j1 ON DATE(Realtime.LogDateTime) = DATE(j1.LogDate)
-- k1: Pm10_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm10_minRT, pm10
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10 ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
k1 ON DATE(Realtime.LogDateTime) = DATE(k1.LogDate)
-- l1: Pm10_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm10_maxRT, pm10
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10 DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
l1 ON DATE(Realtime.LogDateTime) = DATE(l1.LogDate)
-- m1: OutTpm10_1hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_1hr_minRT, pm10_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_1hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
m1 ON DATE(Realtime.LogDateTime) = DATE(m1.LogDate)
-- n1: OutTpm10_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_1hr_maxRT, pm10_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_1hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
n1 ON DATE(Realtime.LogDateTime) = DATE(n1.LogDate)
-- o1: OutTpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_3hr_minRT, pm10_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_3hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
o1 ON DATE(Realtime.LogDateTime) = DATE(o1.LogDate)
-- p1: OutTpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_3hr_maxRT, pm10_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_3hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
p1 ON DATE(Realtime.LogDateTime) = DATE(p1.LogDate)
-- q1: OutTpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_24hr_minRT, pm10_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_24hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
q1 ON DATE(Realtime.LogDateTime) = DATE(q1.LogDate)
-- r1: OutTpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_24hr_maxRT, pm10_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_24hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
r1 ON DATE(Realtime.LogDateTime) = DATE(r1.LogDate)
-- a2: AQIpm2p5_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_minRT, AQIpm2p5
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5 ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
a2 ON DATE(Realtime.LogDateTime) = DATE(a2.LogDate)
-- b2: AQIm2p5_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_maxRT, AQIpm2p5
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5 DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
b2 ON DATE(Realtime.LogDateTime) = DATE(b2.LogDate)
-- c2: AQIpm2p5_1hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_1hr_minRT, AQIpm2p5_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_1hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
c2 ON DATE(Realtime.LogDateTime) = DATE(c2.LogDate)
-- d2: AQIpm2p5_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_1hr_maxRT, AQIpm2p5_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_1hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
d2 ON DATE(Realtime.LogDateTime) = DATE(d2.LogDate)
-- e2: AQIpm2p5_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_3hr_minRT, AQIpm2p5_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_3hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
e2 ON DATE(Realtime.LogDateTime) = DATE(e2.LogDate)
-- f2: AQIpm2p5_3hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_3hr_maxRT, AQIpm2p5_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_3hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
f2 ON DATE(Realtime.LogDateTime) = DATE(f2.LogDate)
-- g2: AQIpm2p5_24hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_24hr_minRT, AQIpm2p5_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_24hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
g2 ON DATE(Realtime.LogDateTime) = DATE(g2.LogDate)
-- h2: AQIpm2p5_24hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_24hr_maxRT, AQIpm2p5_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_24hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
h2 ON DATE(Realtime.LogDateTime) = DATE(h2.LogDate)
-- i2: AQIpm10_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_minRT, AQIpm10
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10 ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
i2 ON DATE(Realtime.LogDateTime) = DATE(i2.LogDate)
-- j2: AQIpm10_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_maxRT, AQIpm10
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10 DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
j2 ON DATE(Realtime.LogDateTime) = DATE(j2.LogDate)
-- k2: AQIpm10_1hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_1hr_minRT, AQIpm10_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_1hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
k2 ON DATE(Realtime.LogDateTime) = DATE(k2.LogDate)
-- l2: AQIpm10_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_1hr_maxRT, AQIpm10_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_1hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
l2 ON DATE(Realtime.LogDateTime) = DATE(l2.LogDate)
-- m2: AQIpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_3hr_minRT, AQIpm10_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_3hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
m2 ON DATE(Realtime.LogDateTime) = DATE(m2.LogDate)
-- n2: AQIpm10_3hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_3hr_maxRT, AQIpm10_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_3hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
n2 ON DATE(Realtime.LogDateTime) = DATE(n2.LogDate)
-- o2: AQIpm10_24hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_24hr_minRT, AQIpm10_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_24hr ASC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
o2 ON DATE(Realtime.LogDateTime) = DATE(o2.LogDate)
-- p2: AQIpm10_24hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_24hr_maxRT, AQIpm10_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_24hr DESC, LogDateTime) rn
FROM Realtime
WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
p2 ON DATE(Realtime.LogDateTime) = DATE(p2.LogDate)
);
-- values from monthly table
DROP TEMPORARY TABLE IF EXISTS monthlyData;
CREATE TEMPORARY TABLE IF NOT EXISTS monthlyData (
LogDate DATE NOT NULL,
OutPm1_avg DECIMAL (5,1) NULL,
OutPm1_min DECIMAL (5,1) NULL,
OutTPm1_min TIME NULL,
OutPm1_max DECIMAL (5,1) NULL,
OutTPm1_max TIME NULL,
OutPm2p5_avg DECIMAL (5,1) NULL,
OutPm2p5_min DECIMAL (5,1) NULL,
OutTPm2p5_min TIME NULL,
OutPm2p5_max DECIMAL (5,1) NULL,
OutTPm2p5_max TIME NULL,
OutPm2p5_1hr_avg DECIMAL (5,1) NULL,
OutPm2p5_1hr_min DECIMAL (5,1) NULL,
OutTpm2p5_1hr_min TIME NULL,
OutPm2p5_1hr_max DECIMAL (5,1) NULL,
OutTpm2p5_1hr_max TIME NULL,
OutPm2p5_3hr_avg DECIMAL (5,1) NULL,
OutPm2p5_3hr_min DECIMAL (5,1) NULL,
OutTpm2p5_3hr_min TIME NULL,
OutPm2p5_3hr_max DECIMAL (5,1) NULL,
OutTpm2p5_3hr_max TIME NULL,
OutPm2p5_24hr_avg DECIMAL (5,1) NULL,
OutPm2p5_24hr_min DECIMAL (5,1) NULL,
OutTpm2p5_24hr_min TIME NULL,
OutPm2p5_24hr_max DECIMAL (5,1) NULL,
OutTpm2p5_24hr_max TIME NULL,
OutPm10_avg DECIMAL (5,1) NULL,
OutPm10_min DECIMAL (5,1) NULL,
OutTPm10_min TIME NULL,
OutPm10_max DECIMAL (5,1) NULL,
OutTPm10_max TIME NULL,
OutPm10_1hr_avg DECIMAL (5,1) NULL,
OutPm10_1hr_min DECIMAL (5,1) NULL,
OutTpm10_1hr_min TIME NULL,
OutPm10_1hr_max DECIMAL (5,1) NULL,
OutTpm10_1hr_max TIME NULL,
OutPm10_3hr_avg DECIMAL (5,1) NULL,
OutPm10_3hr_min DECIMAL (5,1) NULL,
OutTpm10_3hr_min TIME NULL,
OutPm10_3hr_max DECIMAL (5,1) NULL,
OutTpm10_3hr_max TIME NULL,
OutPm10_24hr_avg DECIMAL (5,1) NULL,
OutPm10_24hr_min DECIMAL (5,1) NULL,
OutTpm10_24hr_min TIME NULL,
OutPm10_24hr_max DECIMAL (5,1) NULL,
OutTpm10_24hr_max TIME NULL,
OutAqiPm2p5_avg DECIMAL (5,1) NULL,
OutAqiPm2p5_min DECIMAL (5,1) NULL,
OutTAqiPm2p5_min TIME NULL,
OutAqiPm2p5_max DECIMAL (5,1) NULL,
OutTAqiPm2p5_max TIME NULL,
OutAqiPm2p5_1hr_avg DECIMAL (5,1) NULL,
OutAqiPm2p5_1hr_min DECIMAL (5,1) NULL,
OutTAqiPm2p5_1hr_min TIME NULL,
OutAqiPm2p5_1hr_max DECIMAL (5,1) NULL,
OutTAqiPm2p5_1hr_max TIME NULL,
OutAqiPm2p5_3hr_avg DECIMAL (5,1) NULL,
OutAqiPm2p5_3hr_min DECIMAL (5,1) NULL,
OutTAqiPm2p5_3hr_min TIME NULL,
OutAqiPm2p5_3hr_max DECIMAL (5,1) NULL,
OutTAqiPm2p5_3hr_max TIME NULL,
OutAqiPm2p5_24hr_avg DECIMAL (5,1) NULL,
OutAqiPm2p5_24hr_min DECIMAL (5,1) NULL,
OutTAqiPm2p5_24hr_min TIME NULL,
OutAqiPm2p5_24hr_max DECIMAL (5,1) NULL,
OutTAqiPm2p5_24hr_max TIME NULL,
OutAqiPm10_avg DECIMAL (5,1) NULL,
OutAqiPm10_min DECIMAL (5,1) NULL,
OutTAqiPm10_min TIME NULL,
OutAqiPm10_max DECIMAL (5,1) NULL,
OutTAqiPm10_max TIME NULL,
OutAqiPm10_1hr_avg DECIMAL (5,1) NULL,
OutAqiPm10_1hr_min DECIMAL (5,1) NULL,
OutTAqiPm10_1hr_min TIME NULL,
OutAqiPm10_1hr_max DECIMAL (5,1) NULL,
OutTAqiPm10_1hr_max TIME NULL,
OutAqiPm10_3hr_avg DECIMAL (5,1) NULL,
OutAqiPm10_3hr_min DECIMAL (5,1) NULL,
OutTAqiPm10_3hr_min TIME NULL,
OutAqiPm10_3hr_max DECIMAL (5,1) NULL,
OutTAqiPm10_3hr_max TIME NULL,
OutAqiPm10_24hr_avg DECIMAL (5,1) NULL,
OutAqiPm10_24hr_min DECIMAL (5,1) NULL,
OutTAqiPm10_24hr_min TIME NULL,
OutAqiPm10_24hr_max DECIMAL (5,1) NULL,
OutTAqiPm10_24hr_max TIME NULL
) AS (
SELECT DATE(MonthlyAirQual.LogDateTime) AS LogDate,
ag.OutPm1_avg AS OutPm1_avg,
ROUND(MIN(a1.OutPm1),1) AS OutPm1_min, a1.OutTPm1_min,
ROUND(MAX(b1.OutPm1),1) AS OutPm1_max, b1.OutTPm1_max,
ag.OutPm2p5_avg AS OutPm2p5_avg,
ROUND(MIN(c1.OutPm2p5),1) AS OutPm2p5_min, c1.OutTPm2p5_min,
ROUND(MAX(d1.OutPm2p5),1) AS OutPm2p5_max, d1.OutTPm2p5_max,
ag.OutPm2p5_1hr_avg AS OutPm2p5_1hr_avg,
ROUND(MIN(e1.OutPm2p5_1hr),1) AS OutPm2p5_1hr_min, e1.OutTPm2p5_1hr_min,
ROUND(MAX(f1.OutPm2p5_1hr),1) AS OutPm2p5_1hr_max, f1.OutTpm2p5_1hr_max,
ag.OutPm2p5_3hr_avg AS OutPm2p5_3hr_avg,
ROUND(MIN(g1.OutPm2p5_3hr),1) AS OutPm2p5_3hr_min, g1.OutTPm2p5_3hr_min,
ROUND(MAX(h1.OutPm2p5_3hr),1) AS OutPm2p5_3hr_max, h1.OutTPm2p5_3hr_max,
ag.OutPm2p5_24hr_avg AS OutPm2p5_24hr_avg,
ROUND(MIN(i1.OutPm2p5_24hr),1) AS OutPm2p5_24hr_min, i1.OutTPm2p5_24hr_min,
ROUND(MAX(j1.OutPm2p5_24hr),1) AS OutPm2p5_24hr_max, j1.OutTPm2p5_24hr_max,
ag.OutPm10_avg AS OutPm10_avg,
ROUND(MIN(k1.OutPm10),1) AS OutPm10_min, k1.OutTPm10_min,
ROUND(MAX(l1.OutPm10),1) AS OutPm10_max, l1.OutTPm10_max,
ag.OutPm10_1hr_avg AS OutPm10_1hr_avg,
ROUND(MIN(m1.OutPm10_1hr),1) AS OutPm10_1hr_min, m1.OutTPm10_1hr_min,
ROUND(MAX(n1.OutPm10_1hr),1) AS OutPm10_1hr_max, n1.OutTPm10_1hr_max,
ag.OutPm10_3hr_avg AS OutPm10_3hr_avg,
ROUND(MIN(o1.OutPm10_3hr),1) AS OutPm10_3hr_min, o1.OutTPm10_3hr_min,
ROUND(MAX(p1.OutPm10_3hr),1) AS OutPm10_3hr_max, p1.OutTPm10_3hr_max,
ag.OutPm10_24hr_avg AS OutPm10_24hr_avg,
ROUND(MIN(q1.OutPm10_24hr),1) AS OutPm10_24hr_min, q1.OutTPm10_24hr_min,
ROUND(MAX(r1.OutPm10_24hr),1) AS OutPm10_24hr_max, r1.OutTPm10_24hr_max,
ag.OutAqiPm2p5_avg AS OutAqiPm2p5_avg,
ROUND(MIN(a2.OutAqiPm2p5),1) AS OutAqiPm2p5_min, a2.OutTAqiPm2p5_min,
ROUND(MAX(b2.OutAqiPm2p5),1) AS OutAqiPm2p5_max, b2.OutTAqiPm2p5_max,
ag.OutAqiPm2p5_1hr_avg AS OutAqiPm2p5_1hr_avg,
ROUND(MIN(c2.OutAqiPm2p5_1hr),1) AS OutAqiPm2p5_1hr_min, c2.OutTAqiPm2p5_1hr_min,
ROUND(MAX(d2.OutAqiPm2p5_1hr),1) AS OutAqiPm2p5_1hr_max, d2.OutTAqiPm2p5_1hr_max,
ag.OutAqiPm2p5_3hr_avg AS OutAqiPm2p5_3hr_avg,
ROUND(MIN(e2.OutAqiPm2p5_3hr),1) AS OutAqiPm2p5_3hr_min, e2.OutTAqiPm2p5_3hr_min,
ROUND(MAX(f2.OutAqiPm2p5_3hr),1) AS OutAqiPm2p5_3hr_max, f2.OutTAqiPm2p5_3hr_max,
ag.OutAqiPm2p5_24hr_avg AS OutAqiPm2p5_24hr_avg,
ROUND(MIN(g2.OutAqiPm2p5_24hr),1) AS OutAqiPm2p5_24hr_min, g2.OutTAqiPm2p5_24hr_min,
ROUND(MAX(h2.OutAqiPm2p5_24hr),1) AS OutAqiPm2p5_24hr_max, h2.OutTAqiPm2p5_24hr_max,
ag.OutAqiPm10_avg AS OutAqiPm10_avg,
ROUND(MIN(i2.OutAqiPm10),1) AS OutAqiPm10_min, i2.OutTAqiPm10_min,
ROUND(MAX(j2.OutAqiPm10),1) AS OutAqiPm10_max, j2.OutTAqiPm10_max,
ag.OutAqiPm10_1hr_avg AS OutAqiPm10_1hr_avg,
ROUND(MIN(k2.OutAqiPm10_1hr),1) AS OutAqiPm10_1hr_min, k2.OutTAqiPm10_1hr_min,
ROUND(MAX(l2.OutAqiPm10_1hr),1) AS OutAqiPm10_1hr_max, l2.OutTAqiPm10_1hr_max,
ag.OutAqiPm10_3hr_avg AS OutAqiPm10_3hr_avg,
ROUND(MIN(m2.OutAqiPm10_3hr),1) AS OutAqiPm10_3hr_min, m2.OutTAqiPm10_3hr_min,
ROUND(MAX(n2.OutAqiPm10_3hr),1) AS OutAqiPm10_3hr_max, n2.OutTAqiPm10_3hr_max,
ag.OutAqiPm10_24hr_avg AS OutAqiPm10_24hr_avg,
ROUND(MIN(o2.OutAqiPm10_24hr),1) AS OutAqiPm10_24hr_min, o2.OutTAqiPm10_24hr_min,
ROUND(MAX(p2.OutAqiPm10_24hr),1) AS OutAqiPm10_24hr_max, p2.OutTAqiPm10_24hr_max
FROM MonthlyAirQual
INNER JOIN(
-- average values
SELECT MonthlyAirQual.LogDateTime AS LogDate,
ROUND(AVG(MonthlyAirQual.OutPm1),1) AS OutPm1_avg,
ROUND(AVG(MonthlyAirQual.OutPm2p5),1) AS OutPm2p5_avg,
ROUND(AVG(MonthlyAirQual.OutPm2p5_1hr),1) AS OutPm2p5_1hr_avg,
ROUND(AVG(MonthlyAirQual.OutPm2p5_3hr),1) AS OutPm2p5_3hr_avg,
ROUND(AVG(MonthlyAirQual.OutPm2p5_24hr),1) AS OutPm2p5_24hr_avg,
ROUND(AVG(MonthlyAirQual.OutPm10),1) AS OutPm10_avg,
ROUND(AVG(MonthlyAirQual.OutPm10_1hr),1) AS OutPm10_1hr_avg,
ROUND(AVG(MonthlyAirQual.OutPm10_3hr),1) AS OutPm10_3hr_avg,
ROUND(AVG(MonthlyAirQual.OutPm10_24hr),1) AS OutPm10_24hr_avg,
ROUND(AVG(MonthlyAirQual.OutAqiPm2p5),1) AS OutAqiPm2p5_avg,
ROUND(AVG(MonthlyAirQual.OutAqiPm2p5_1hr),1) AS OutAqiPm2p5_1hr_avg,
ROUND(AVG(MonthlyAirQual.OutAqiPm2p5_3hr),1) AS OutAqiPm2p5_3hr_avg,
ROUND(AVG(MonthlyAirQual.OutAqiPm2p5_24hr),1) AS OutAqiPm2p5_24hr_avg,
ROUND(AVG(MonthlyAirQual.OutAqiPm10),1) AS OutAqiPm10_avg,
ROUND(AVG(MonthlyAirQual.OutAqiPm10_1hr),1) AS OutAqiPm10_1hr_avg,
ROUND(AVG(MonthlyAirQual.OutAqiPm10_3hr),1) AS OutAqiPm10_3hr_avg,
ROUND(AVG(MonthlyAirQual.OutAqiPm10_24hr),1) AS OutAqiPm10_24hr_avg
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
GROUP BY DAY(MonthlyAirQual.LogDateTime), MONTH(MonthlyAirQual.LogDateTime), YEAR(MonthlyAirQual.LogDateTime)
) ag ON DATE(MonthlyAirQual.LogDateTime) = DATE(ag.LogDate)
-- a1: Pm1_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm1_min, OutPm1
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm1 ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
a1 ON (MonthlyAirQual.LogDateTime) = (a1.LogDate)
-- b1: Pm1_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm1_max, OutPm1
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm1 DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
b1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(b1.LogDate)
-- c1: Pm2p5_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm2p5_min, OutPm2p5
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5 ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
c1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(c1.LogDate)
-- d1: Pm2p5_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm2p5_max, OutPm2p5
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5 DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
d1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(d1.LogDate)
-- e1: Pm2p5_1hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_1hr_min, OutPm2p5_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_1hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
e1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(e1.LogDate)
-- f1: Pm2p5_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_1hr_max, OutPm2p5_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_1hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
f1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(f1.LogDate)
-- g1: Pm2p5_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_3hr_min, OutPm2p5_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_3hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
g1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(g1.LogDate)
-- h1: Pm2p5_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_3hr_max, OutPm2p5_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_3hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
h1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(h1.LogDate)
-- i1: Pm2p5_24hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_24hr_min, OutPm2p5_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_24hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
i1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(i1.LogDate)
-- j1: Pm2p5_24hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm2p5_24hr_max, OutPm2p5_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_24hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
j1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(j1.LogDate)
-- k1: Pm10_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm10_min, OutPm10
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10 ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
k1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(k1.LogDate)
-- l1: Pm10_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTPm10_max, OutPm10
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10 DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
l1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(l1.LogDate)
-- m1: OutTpm10_1hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_1hr_min, OutPm10_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_1hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
m1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(m1.LogDate)
-- n1: OutTpm10_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_1hr_max, OutPm10_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_1hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
n1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(n1.LogDate)
-- o1: OutTpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_3hr_min, OutPm10_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_3hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
o1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(o1.LogDate)
-- p1: OutTpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_3hr_max, OutPm10_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_3hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
p1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(p1.LogDate)
-- q1: OutTpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_24hr_min, OutPm10_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_24hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
q1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(q1.LogDate)
-- r1: OutTpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTpm10_24hr_max, OutPm10_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_24hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
r1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(r1.LogDate)
-- a2: AQIpm2p5_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_min, OutAqiPm2p5
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5 ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
a2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(a2.LogDate)
-- b2: AQIm2p5_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_max, OutAqiPm2p5
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5 DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
b2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(b2.LogDate)
-- c2: AQIpm2p5_1hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_1hr_min, OutAqiPm2p5_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_1hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
c2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(c2.LogDate)
-- d2: AQIpm2p5_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_1hr_max, OutAqiPm2p5_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_1hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
d2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(d2.LogDate)
-- e2: AQIpm2p5_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_3hr_min, OutAqiPm2p5_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_3hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
e2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(e2.LogDate)
-- f2: AQIpm2p5_3hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_3hr_max, OutAqiPm2p5_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_3hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
f2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(f2.LogDate)
-- g2: AQIpm2p5_24hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_24hr_min, OutAqiPm2p5_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_24hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
g2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(g2.LogDate)
-- h2: AQIpm2p5_24hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm2p5_24hr_max, OutAqiPm2p5_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_24hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
h2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(h2.LogDate)
-- i2: AQIpm10_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_min, OutAqiPm10
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10 ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
i2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(i2.LogDate)
-- j2: AQIpm10_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_max, OutAqiPm10
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10 DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
j2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(j2.LogDate)
-- k2: AQIpm10_1hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_1hr_min, OutAqiPm10_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_1hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
k2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(k2.LogDate)
-- l2: AQIpm10_1hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_1hr_max, OutAqiPm10_1hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_1hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
l2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(l2.LogDate)
-- m2: AQIpm10_3hr_minRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_3hr_min, OutAqiPm10_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_3hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
m2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(m2.LogDate)
-- n2: AQIpm10_3hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_3hr_max, OutAqiPm10_3hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_3hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
n2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(n2.LogDate)
-- o2: AQIpm10_24hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_24hr_min, OutAqiPm10_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_24hr ASC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
o2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(o2.LogDate)
-- p2: AQIpm10_24hr_maxRT
INNER JOIN(
SELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,"%H:%i") AS OutTAqiPm10_24hr_max, OutAqiPm10_24hr
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_24hr DESC, LogDateTime) rn
FROM MonthlyAirQual
WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()
) t
WHERE t.rn = 1 )
p2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(p2.LogDate)
GROUP BY DAY(MonthlyAirQual.LogDateTime), MONTH(MonthlyAirQual.LogDateTime), YEAR(MonthlyAirQual.LogDateTime)
);
-- insert data from temporary tables
INSERT IGNORE INTO DailyAirQual (
LogDate,
OutPm1_avg,
OutPm1_min,
OutTPm1_min,
OutPm1_max,
OutTPm1_max,
OutPm2p5_avg,
OutPm2p5_min,
OutTPm2p5_min,
OutPm2p5_max,
OutTPm2p5_max,
OutPm2p5_1hr_avg,
OutPm2p5_1hr_min,
OutTpm2p5_1hr_min,
OutPm2p5_1hr_max,
OutTpm2p5_1hr_max,
OutPm2p5_3hr_avg,
OutPm2p5_3hr_min,
OutTpm2p5_3hr_min,
OutPm2p5_3hr_max,
OutTpm2p5_3hr_max,
OutPm2p5_24hr_avg,
OutPm2p5_24hr_min,
OutTpm2p5_24hr_min,
OutPm2p5_24hr_max,
OutTpm2p5_24hr_max,
OutPm10_avg,
OutPm10_min,
OutTPm10_min,
OutPm10_max,
OutTPm10_max,
OutPm10_1hr_avg,
OutPm10_1hr_min,
OutTpm10_1hr_min,
OutPm10_1hr_max,
OutTpm10_1hr_max,
OutPm10_3hr_avg,
OutPm10_3hr_min,
OutTpm10_3hr_min,
OutPm10_3hr_max,
OutTpm10_3hr_max,
OutPm10_24hr_avg,
OutPm10_24hr_min,
OutTpm10_24hr_min,
OutPm10_24hr_max,
OutTpm10_24hr_max,
OutAqiPm2p5_avg,
OutAqiPm2p5_min,
OutTAqiPm2p5_min,
OutAqiPm2p5_max,
OutTAqiPm2p5_max,
OutAqiPm2p5_1hr_avg,
OutAqiPm2p5_1hr_min,
OutTAqiPm2p5_1hr_min,
OutAqiPm2p5_1hr_max,
OutTAqiPm2p5_1hr_max,
OutAqiPm2p5_3hr_avg,
OutAqiPm2p5_3hr_min,
OutTAqiPm2p5_3hr_min,
OutAqiPm2p5_3hr_max,
OutTAqiPm2p5_3hr_max,
OutAqiPm2p5_24hr_avg,
OutAqiPm2p5_24hr_min,
OutTAqiPm2p5_24hr_min,
OutAqiPm2p5_24hr_max,
OutTAqiPm2p5_24hr_max,
OutAqiPm10_avg,
OutAqiPm10_min,
OutTAqiPm10_min,
OutAqiPm10_max,
OutTAqiPm10_max,
OutAqiPm10_1hr_avg,
OutAqiPm10_1hr_min,
OutTAqiPm10_1hr_min,
OutAqiPm10_1hr_max,
OutTAqiPm10_1hr_max,
OutAqiPm10_3hr_avg,
OutAqiPm10_3hr_min,
OutTAqiPm10_3hr_min,
OutAqiPm10_3hr_max,
OutTAqiPm10_3hr_max,
OutAqiPm10_24hr_avg,
OutAqiPm10_24hr_min,
OutTAqiPm10_24hr_min,
OutAqiPm10_24hr_max,
OutTAqiPm10_24hr_max
)
-- use GREATEST and COALESCE to return the highest non null value
SELECT DATE(monthlyData.LogDate) AS LogDate,
ROUND(GREATEST(COALESCE(rtData.OutPm1_avgRT,0),monthlyData.OutPm1_avg),1) AS OutPm1_avg,
ROUND(GREATEST(COALESCE(rtData.OutPm1_minRT,0),monthlyData.OutPm1_min),1) AS OutPm1_min,
CASE WHEN GREATEST(COALESCE(rtData.OutPm1_minRT,0),monthlyData.OutPm1_min) = rtData.OutPm1_minRT THEN rtData.OutTPm1_minRT ELSE monthlyData.OutTPm1_min END AS OutTPm1_min,
ROUND(GREATEST(COALESCE(rtData.OutPm1_maxRT,0),monthlyData.OutPm1_max),1) AS OutPm1_max,
CASE WHEN GREATEST(COALESCE(rtData.OutPm1_maxRT,0),monthlyData.OutPm1_max) = rtData.OutPm1_maxRT THEN rtData.OutTPm1_maxRT ELSE monthlyData.OutTPm1_max END AS OutTPm1_max,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_avgRT,0),monthlyData.OutPm2p5_avg),1) AS OutPm2p5_avg,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_minRT,0),monthlyData.OutPm2p5_min),1) AS OutPm2p5_min,
CASE WHEN GREATEST(COALESCE(rtData.OutPm2p5_minRT,0),monthlyData.OutPm2p5_min) = rtData.OutPm2p5_minRT THEN rtData.OutTPm2p5_minRT ELSE monthlyData.OutTPm2p5_min END AS OutTPm2p5_min,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_maxRT,0),monthlyData.OutPm2p5_max),1) AS OutPm2p5_max,
CASE WHEN GREATEST(COALESCE(rtData.OutPm2p5_maxRT,0),monthlyData.OutPm2p5_max) = rtData.OutPm2p5_maxRT THEN rtData.OutTPm2p5_maxRT ELSE monthlyData.OutTPm2p5_max END AS OutTPm2p5_min,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_1hr_avgRT,0),monthlyData.OutPm2p5_1hr_avg),1) AS OutPm2p5_1hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_1hr_minRT,0),monthlyData.OutPm2p5_1hr_min),1) AS OutPm2p5_1hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutPm2p5_1hr_minRT,0),monthlyData.OutPm2p5_1hr_min) = rtData.OutPm2p5_1hr_minRT THEN rtData.OutTPm2p5_1hr_minRT ELSE monthlyData.OutTPm2p5_1hr_min END AS OutTPm2p5_1hr_min,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_1hr_maxRT,0),monthlyData.OutPm2p5_1hr_max),1) AS OutPm2p5_1hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutPm2p5_1hr_maxRT,0),monthlyData.OutPm2p5_1hr_max) = rtData.OutPm2p5_1hr_maxRT THEN rtData.OutTPm2p5_1hr_maxRT ELSE monthlyData.OutTPm2p5_1hr_max END AS OutTPm2p5_1hr_max,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_3hr_avgRT,0),monthlyData.OutPm2p5_3hr_avg),1) AS OutPm2p5_3hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_3hr_minRT,0),monthlyData.OutPm2p5_3hr_min),1) AS OutPm2p5_3hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutPm2p5_3hr_minRT,0),monthlyData.OutPm2p5_3hr_min) = rtData.OutPm2p5_3hr_minRT THEN rtData.OutTPm2p5_3hr_minRT ELSE monthlyData.OutTPm2p5_3hr_min END AS OutTPm2p5_3hr_min,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_3hr_maxRT,0),monthlyData.OutPm2p5_3hr_max),1) AS OutPm2p5_3hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutPm2p5_3hr_maxRT,0),monthlyData.OutPm2p5_3hr_max) = rtData.OutPm2p5_3hr_maxRT THEN rtData.OutTPm2p5_3hr_maxRT ELSE monthlyData.OutTPm2p5_3hr_max END AS OutTPm2p5_3hr_max,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_24hr_avgRT,0),monthlyData.OutPm2p5_24hr_avg),1) AS OutPm2p5_24hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_24hr_minRT,0),monthlyData.OutPm2p5_24hr_min),1) AS OutPm2p5_24hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutPm2p5_24hr_minRT,0),monthlyData.OutPm2p5_24hr_min) = rtData.OutPm2p5_24hr_minRT THEN rtData.OutTPm2p5_24hr_minRT ELSE monthlyData.OutTPm2p5_24hr_min END AS OutTPm2p5_24hr_min,
ROUND(GREATEST(COALESCE(rtData.OutPm2p5_24hr_maxRT,0),monthlyData.OutPm2p5_24hr_max),1) AS OutPm2p5_24hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutPm2p5_24hr_maxRT,0),monthlyData.OutPm2p5_24hr_max) = rtData.OutPm2p5_24hr_maxRT THEN rtData.OutTPm2p5_24hr_maxRT ELSE monthlyData.OutTPm2p5_24hr_max END AS OutTPm2p5_24hr_max,
ROUND(GREATEST(COALESCE(rtData.OutPm10_avgRT,0),monthlyData.OutPm10_avg),1) AS OutPm10_avg,
ROUND(GREATEST(COALESCE(rtData.OutPm10_minRT,0),monthlyData.OutPm10_min),1) AS OutPm10_min,
CASE WHEN GREATEST(COALESCE(rtData.OutPm10_minRT,0),monthlyData.OutPm10_min) = rtData.OutPm10_minRT THEN rtData.OutTPm10_minRT ELSE monthlyData.OutTPm10_min END AS OutTPm10_min,
ROUND(GREATEST(COALESCE(rtData.OutPm10_maxRT,0),monthlyData.OutPm10_max),1) AS OutPm10_max,
CASE WHEN GREATEST(COALESCE(rtData.OutPm10_maxRT,0),monthlyData.OutPm10_max) = rtData.OutPm10_maxRT THEN rtData.OutTPm10_maxRT ELSE monthlyData.OutTPm10_max END AS OutTPm10_max,
ROUND(GREATEST(COALESCE(rtData.OutPm10_1hr_avgRT,0),monthlyData.OutPm10_1hr_avg),1) AS OutPm10_1hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutPm10_1hr_minRT,0),monthlyData.OutPm10_1hr_min),1) AS OutPm10_1hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutPm10_1hr_minRT,0),monthlyData.OutPm10_1hr_min) = rtData.OutPm10_1hr_minRT THEN rtData.OutTPm10_1hr_minRT ELSE monthlyData.OutTPm10_1hr_min END AS OutTPm10_1hr_min,
ROUND(GREATEST(COALESCE(rtData.OutPm10_1hr_maxRT,0),monthlyData.OutPm10_1hr_max),1) AS OutPm10_1hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutPm10_1hr_maxRT,0),monthlyData.OutPm10_1hr_max) = rtData.OutPm10_1hr_maxRT THEN rtData.OutTPm10_1hr_maxRT ELSE monthlyData.OutTPm10_1hr_max END AS OutTPm10_1hr_max,
ROUND(GREATEST(COALESCE(rtData.OutPm10_3hr_avgRT,0),monthlyData.OutPm10_3hr_avg),1) AS OutPm10_3hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutPm10_3hr_minRT,0),monthlyData.OutPm10_3hr_min),1) AS OutPm10_3hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutPm10_3hr_minRT,0),monthlyData.OutPm10_3hr_min) = rtData.OutPm10_3hr_minRT THEN rtData.OutTPm10_3hr_minRT ELSE monthlyData.OutTPm10_3hr_min END AS OutTPm10_3hr_min,
ROUND(GREATEST(COALESCE(rtData.OutPm10_3hr_maxRT,0),monthlyData.OutPm10_3hr_max),1) AS OutPm10_3hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutPm10_3hr_maxRT,0),monthlyData.OutPm10_3hr_max) = rtData.OutPm10_3hr_maxRT THEN rtData.OutTPm10_3hr_maxRT ELSE monthlyData.OutTPm10_3hr_max END AS OutTPm10_3hr_max,
ROUND(GREATEST(COALESCE(rtData.OutPm10_24hr_avgRT,0),monthlyData.OutPm10_24hr_avg),1) AS OutPm10_24hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutPm10_24hr_minRT,0),monthlyData.OutPm10_24hr_min),1) AS OutPm10_24hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutPm10_24hr_minRT,0),monthlyData.OutPm10_24hr_min) = rtData.OutPm10_24hr_minRT THEN rtData.OutTPm10_24hr_minRT ELSE monthlyData.OutTPm10_24hr_min END AS OutTPm10_24hr_min,
ROUND(GREATEST(COALESCE(rtData.OutPm10_24hr_maxRT,0),monthlyData.OutPm10_24hr_max),1) AS OutPm10_24hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutPm10_24hr_maxRT,0),monthlyData.OutPm10_24hr_max) = rtData.OutPm10_24hr_maxRT THEN rtData.OutTPm10_24hr_maxRT ELSE monthlyData.OutTPm10_24hr_max END AS OutTPm10_24hr_max,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_avgRT,0),monthlyData.OutAqiPm2p5_avg),1) AS OutAqiPm2p5_avg,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_minRT,0),monthlyData.OutAqiPm2p5_min),1) AS OutAqiPm2p5_min,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm2p5_minRT,0),monthlyData.OutAqiPm2p5_min) = rtData.OutAqiPm2p5_minRT THEN rtData.OutTAqiPm2p5_minRT ELSE monthlyData.OutTAqiPm2p5_min END AS OutTAqiPm2p5_min,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_maxRT,0),monthlyData.OutAqiPm2p5_max),1) AS OutAqiPm2p5_max,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm2p5_maxRT,0),monthlyData.OutAqiPm2p5_max) = rtData.OutAqiPm2p5_maxRT THEN rtData.OutTAqiPm2p5_maxRT ELSE monthlyData.OutTAqiPm2p5_max END AS OutTAqiPm2p5_max,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_1hr_avgRT,0),monthlyData.OutAqiPm2p5_1hr_avg),1) AS OutAqiPm2p5_1hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_1hr_minRT,0),monthlyData.OutAqiPm2p5_1hr_min),1) AS OutAqiPm2p5_1hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm2p5_1hr_minRT,0),monthlyData.OutAqiPm2p5_1hr_min) = rtData.OutAqiPm2p5_1hr_minRT THEN rtData.OuttAqiPm2p5_1hr_minRT ELSE monthlyData.OuttAqiPm2p5_1hr_min END AS OuttAqiPm2p5_1hr_min,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_1hr_maxRT,0),monthlyData.OutAqiPm2p5_1hr_max),1) AS OutAqiPm2p5_1hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm2p5_1hr_maxRT,0),monthlyData.OutAqiPm2p5_1hr_max) = rtData.OutAqiPm2p5_1hr_maxRT THEN rtData.OuttAqiPm2p5_1hr_maxRT ELSE monthlyData.OuttAqiPm2p5_1hr_max END AS OuttAqiPm2p5_1hr_max,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_3hr_avgRT,0),monthlyData.OutAqiPm2p5_3hr_avg),1) AS OutAqiPm2p5_3hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_3hr_minRT,0),monthlyData.OutAqiPm2p5_3hr_min),1) AS OutAqiPm2p5_3hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm2p5_3hr_minRT,0),monthlyData.OutAqiPm2p5_3hr_min) = rtData.OutAqiPm2p5_3hr_minRT THEN rtData.OuttAqiPm2p5_3hr_minRT ELSE monthlyData.OuttAqiPm2p5_3hr_min END AS OuttAqiPm2p5_3hr_min,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_3hr_maxRT,0),monthlyData.OutAqiPm2p5_3hr_max),1) AS OutAqiPm2p5_3hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm2p5_3hr_maxRT,0),monthlyData.OutAqiPm2p5_3hr_max) = rtData.OutAqiPm2p5_3hr_maxRT THEN rtData.OuttAqiPm2p5_3hr_maxRT ELSE monthlyData.OuttAqiPm2p5_3hr_max END AS OuttAqiPm2p5_3hr_max,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_24hr_avgRT,0),monthlyData.OutAqiPm2p5_24hr_avg),1) AS OutAqiPm2p5_24hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_24hr_minRT,0),monthlyData.OutAqiPm2p5_24hr_min),1) AS OutAqiPm2p5_24hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm2p5_24hr_minRT,0),monthlyData.OutAqiPm2p5_24hr_min) = rtData.OutAqiPm2p5_24hr_minRT THEN rtData.OuttAqiPm2p5_24hr_minRT ELSE monthlyData.OuttAqiPm2p5_24hr_min END AS OuttAqiPm2p5_24hr_min,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_24hr_maxRT,0),monthlyData.OutAqiPm2p5_24hr_max),1) AS OutAqiPm2p5_24hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm2p5_24hr_maxRT,0),monthlyData.OutAqiPm2p5_24hr_max) = rtData.OutAqiPm2p5_24hr_maxRT THEN rtData.OuttAqiPm2p5_24hr_maxRT ELSE monthlyData.OuttAqiPm2p5_24hr_max END AS OuttAqiPm2p5_24hr_max,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_avgRT,0),monthlyData.OutAqiPm10_avg),1) AS OutAqiPm10_avg,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_minRT,0),monthlyData.OutAqiPm10_min),1) AS OutAqiPm10_min,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm10_minRT,0),monthlyData.OutAqiPm10_min) = rtData.OutAqiPm10_minRT THEN rtData.OutTAqiPm10_minRT ELSE monthlyData.OutTAqiPm10_min END AS OutTAqiPm10_min,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_maxRT,0),monthlyData.OutAqiPm10_max),1) AS OutAqiPm10_max,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm10_maxRT,0),monthlyData.OutAqiPm10_max) = rtData.OutAqiPm10_maxRT THEN rtData.OutTAqiPm10_maxRT ELSE monthlyData.OutTAqiPm10_max END AS OutTAqiPm10_max,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_1hr_avgRT,0),monthlyData.OutAqiPm10_1hr_avg),1) AS OutAqiPm10_1hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_1hr_minRT,0),monthlyData.OutAqiPm10_1hr_min),1) AS OutAqiPm10_1hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm10_1hr_minRT,0),monthlyData.OutAqiPm10_1hr_min) = rtData.OutAqiPm10_1hr_minRT THEN rtData.OuttAqiPm10_1hr_minRT ELSE monthlyData.OuttAqiPm10_1hr_min END AS OuttAqiPm10_1hr_min,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_1hr_maxRT,0),monthlyData.OutAqiPm10_1hr_max),1) AS OutAqiPm10_1hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm10_1hr_maxRT,0),monthlyData.OutAqiPm10_1hr_max) = rtData.OutAqiPm10_1hr_maxRT THEN rtData.OuttAqiPm10_1hr_maxRT ELSE monthlyData.OuttAqiPm10_1hr_max END AS OuttAqiPm10_1hr_max,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_3hr_avgRT,0),monthlyData.OutAqiPm10_3hr_avg),1) AS OutAqiPm10_3hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_3hr_minRT,0),monthlyData.OutAqiPm10_3hr_min),1) AS OutAqiPm10_3hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm10_3hr_minRT,0),monthlyData.OutAqiPm10_3hr_min) = rtData.OutAqiPm10_3hr_minRT THEN rtData.OuttAqiPm10_3hr_minRT ELSE monthlyData.OuttAqiPm10_3hr_min END AS OuttAqiPm10_3hr_min,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_3hr_maxRT,0),monthlyData.OutAqiPm10_3hr_max),1) AS OutAqiPm10_3hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm10_3hr_maxRT,0),monthlyData.OutAqiPm10_3hr_max) = rtData.OutAqiPm10_3hr_maxRT THEN rtData.OuttAqiPm10_3hr_maxRT ELSE monthlyData.OuttAqiPm10_3hr_max END AS OuttAqiPm10_3hr_max,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_24hr_avgRT,0),monthlyData.OutAqiPm10_24hr_avg),1) AS OutAqiPm10_24hr_avg,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_24hr_minRT,0),monthlyData.OutAqiPm10_24hr_min),1) AS OutAqiPm10_24hr_min,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm10_24hr_minRT,0),monthlyData.OutAqiPm10_24hr_min) = rtData.OutAqiPm10_24hr_minRT THEN rtData.OuttAqiPm10_24hr_minRT ELSE monthlyData.OuttAqiPm10_24hr_min END AS OuttAqiPm10_24hr_min,
ROUND(GREATEST(COALESCE(rtData.OutAqiPm10_24hr_maxRT,0),monthlyData.OutAqiPm10_24hr_max),1) AS OutAqiPm10_24hr_max,
CASE WHEN GREATEST(COALESCE(rtData.OutAqiPm10_24hr_maxRT,0),monthlyData.OutAqiPm10_24hr_max) = rtData.OutAqiPm10_24hr_maxRT THEN rtData.OuttAqiPm10_24hr_maxRT ELSE monthlyData.OuttAqiPm10_24hr_max END AS OuttAqiPm10_24hr_max
FROM rtData
-- join the monthly data to the realtime data
RIGHT JOIN monthlyData ON DAY(rtData.LogDateRT) = DAY(monthlyData.LogDate) AND MONTH(rtData.LogDateRT) = MONTH(monthlyData.LogDate) AND YEAR(rtData.LogDateRT) = YEAR(monthlyData.LogDate)
GROUP BY DAY(monthlyData.LogDate), MONTH(monthlyData.LogDate), YEAR(monthlyData.LogDate);
COMMIT;
END$$
DELIMITER ;