{"id":231,"date":"2021-02-17T07:00:00","date_gmt":"2021-02-16T21:00:00","guid":{"rendered":"https:\/\/fernygroveweather.com\/blog\/?p=231"},"modified":"2021-02-17T21:11:50","modified_gmt":"2021-02-17T11:11:50","slug":"new-air-quality-statistics","status":"publish","type":"post","link":"https:\/\/fernygroveweather.com\/blog\/2021\/02\/17\/new-air-quality-statistics\/","title":{"rendered":"New air quality statistics"},"content":{"rendered":"\n

The air quality data we recently began to measure has been expanded to include statistics for this month, this year and all-time.  This data is now available on the this month and year<\/a> and all-time record<\/a> pages.<\/p>\n\n\n\n

In the short period that we have been measuring air quality the air is exceptionally clean. For the vast majority of the time the particulates and air quality index values have been extremely low (good) and have been elevated to a slight degree only for fairly short periods of time. <\/p>\n\n\n\n

To include these new statistics changes were required to be made to our database. Since the recording of air quality began from 26 December 2020, a routine is ran at the start of each day to fetch the data from the previous day to insert a new row into a database table for that day. However generally only the averages and maximums were shown, without the earliest time of the occurrence. With these improvements now the averages, minimum and maximums are measured for each day along with the time of minimums and maximums.<\/p>\n\n\n\n

However, it should noted that these additions began from 7 February 2021, because we only retain the real-time data for 7 days (for data storage reasons). Therefore the time of occurrence will not be shown for statistics before this date. Also we collecting a vast range of daily parameters, most of which haven’t been published to a great deal on the web site, but is being collected for completeness and for future analysis.<\/p>\n\n\n\n

To provide a bit more information about how this works a MySQL database procedure (as shown below) is scheduled to run at the start of each day to insert the previous day data into a table of daily air quality averages and extremes. This uses the ROW_NUMBER window function to calculate the earliest time for each individual minimum and maximum for each day. A query for each of one of these calculations are then joined to a query that essentially merges these calculations into one table. Because these calculations are rather expensive a variable retrieves the latest date of data already inserted into the data to limit the quantity of data that is calculated to only the data not already captured in the daily table.<\/p>\n\n\n\n

These queries are both performed on the real-time table (every 10 seconds) and an 5 minute interval logged data table, with the results inserted into temporary tables. These two temporary tables are then used to determine the greatest value of each of these data sources to insert the data for each column of data. This is so that the data is suitably captured due any possible network outages that may affect the currency of data. The SQL COALESCE function is used to return the first non- null value from these data sources, with preference provided to the Real-time table.<\/p>\n\n\n\n

<\/div>\n\n\n\n
<\/div>\n\n\n\n
DELIMITER $$\nCREATE DEFINER=`root`@`localhost` PROCEDURE `PopulateDailyAirQual`()\n    COMMENT 'Procedure to populate daily air quality  '\nBEGIN\nDECLARE exit handler for SQLEXCEPTION\n \tBEGIN\n  \tGET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, \n   \t\t@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;\n  \tSET @full_error = CONCAT(\"ERROR \", @errno, \" (\", @sqlstate, \"): \", @text);\n  \tSELECT @full_error;\nEND;\n\n    \nSET time_zone=\"+10:00\";\n\n-- determine the most recent data already inserted into table\nSET @lastUpdated = (select DATE(LogDate) FROM DailyAirQual ORDER BY LogDate DESC LIMIT 1);\n \n-- values from realtime table   \nDROP TEMPORARY TABLE IF EXISTS rtData;\nCREATE TEMPORARY TABLE IF NOT EXISTS rtData (\nLogDateRT DATE NOT NULL,          \nOutPm1_avgRT DECIMAL (5,1) NULL,          \nOutPm1_minRT DECIMAL (5,1) NULL,            \nOutTPm1_minRT TIME NULL,         \nOutPm1_maxRT DECIMAL (5,1) NULL,           \nOutTPm1_maxRT TIME NULL,       \nOutPm2p5_avgRT DECIMAL (5,1) NULL,         \nOutPm2p5_minRT DECIMAL (5,1) NULL,        \nOutTPm2p5_minRT TIME NULL,       \nOutPm2p5_maxRT DECIMAL (5,1) NULL,        \nOutTPm2p5_maxRT TIME NULL,     \nOutPm2p5_1hr_avgRT DECIMAL (5,1) NULL,     \nOutPm2p5_1hr_minRT DECIMAL (5,1) NULL,    \nOutTpm2p5_1hr_minRT TIME NULL,  \nOutPm2p5_1hr_maxRT DECIMAL (5,1) NULL,    \nOutTpm2p5_1hr_maxRT TIME NULL,  \nOutPm2p5_3hr_avgRT DECIMAL (5,1) NULL,    \nOutPm2p5_3hr_minRT DECIMAL (5,1) NULL,    \nOutTpm2p5_3hr_minRT TIME NULL,\nOutPm2p5_3hr_maxRT DECIMAL (5,1) NULL,    \nOutTpm2p5_3hr_maxRT TIME NULL,  \nOutPm2p5_24hr_avgRT DECIMAL (5,1) NULL,   \nOutPm2p5_24hr_minRT DECIMAL (5,1) NULL,    \nOutTpm2p5_24hr_minRT TIME NULL,  \nOutPm2p5_24hr_maxRT DECIMAL (5,1) NULL,    \nOutTpm2p5_24hr_maxRT TIME NULL, \nOutPm10_avgRT DECIMAL (5,1) NULL,           \nOutPm10_minRT DECIMAL (5,1) NULL,          \nOutTPm10_minRT TIME NULL,   \nOutPm10_maxRT DECIMAL (5,1) NULL,         \nOutTPm10_maxRT TIME NULL,      \nOutPm10_1hr_avgRT DECIMAL (5,1) NULL,    \nOutPm10_1hr_minRT DECIMAL (5,1) NULL,   \nOutTpm10_1hr_minRT TIME NULL,  \nOutPm10_1hr_maxRT DECIMAL (5,1) NULL,      \nOutTpm10_1hr_maxRT TIME NULL,   \nOutPm10_3hr_avgRT DECIMAL (5,1) NULL,     \nOutPm10_3hr_minRT DECIMAL (5,1) NULL,     \nOutTpm10_3hr_minRT TIME NULL,   \nOutPm10_3hr_maxRT DECIMAL (5,1) NULL,     \nOutTpm10_3hr_maxRT TIME NULL,    \nOutPm10_24hr_avgRT DECIMAL (5,1) NULL,    \nOutPm10_24hr_minRT DECIMAL (5,1) NULL,   \nOutTpm10_24hr_minRT TIME NULL,   \nOutPm10_24hr_maxRT DECIMAL (5,1) NULL,   \nOutTpm10_24hr_maxRT TIME NULL,  \nOutAqiPm2p5_avgRT DECIMAL (5,1) NULL,             \nOutAqiPm2p5_minRT DECIMAL (5,1) NULL, \nOutTAqiPm2p5_minRT TIME NULL,  \nOutAqiPm2p5_maxRT DECIMAL (5,1) NULL,     \nOutTAqiPm2p5_maxRT TIME NULL,    \nOutAqiPm2p5_1hr_avgRT DECIMAL (5,1) NULL,          \nOutAqiPm2p5_1hr_minRT DECIMAL (5,1) NULL, \nOutTAqiPm2p5_1hr_minRT TIME NULL,\nOutAqiPm2p5_1hr_maxRT DECIMAL (5,1) NULL, \nOutTAqiPm2p5_1hr_maxRT TIME NULL,\nOutAqiPm2p5_3hr_avgRT DECIMAL (5,1) NULL,          \nOutAqiPm2p5_3hr_minRT DECIMAL (5,1) NULL,  \nOutTAqiPm2p5_3hr_minRT TIME NULL,\nOutAqiPm2p5_3hr_maxRT DECIMAL (5,1) NULL,\nOutTAqiPm2p5_3hr_maxRT TIME NULL,\nOutAqiPm2p5_24hr_avgRT DECIMAL (5,1) NULL,          \nOutAqiPm2p5_24hr_minRT DECIMAL (5,1) NULL,\nOutTAqiPm2p5_24hr_minRT TIME NULL,\nOutAqiPm2p5_24hr_maxRT DECIMAL (5,1) NULL,\nOutTAqiPm2p5_24hr_maxRT TIME NULL,\nOutAqiPm10_avgRT DECIMAL (5,1) NULL,            \nOutAqiPm10_minRT DECIMAL (5,1) NULL,    \nOutTAqiPm10_minRT TIME NULL,     \nOutAqiPm10_maxRT DECIMAL (5,1) NULL,     \nOutTAqiPm10_maxRT TIME NULL,     \nOutAqiPm10_1hr_avgRT DECIMAL (5,1) NULL,           \nOutAqiPm10_1hr_minRT DECIMAL (5,1) NULL,   \nOutTAqiPm10_1hr_minRT TIME NULL,\nOutAqiPm10_1hr_maxRT DECIMAL (5,1) NULL, \nOutTAqiPm10_1hr_maxRT TIME NULL,\nOutAqiPm10_3hr_avgRT DECIMAL (5,1) NULL,        \nOutAqiPm10_3hr_minRT DECIMAL (5,1) NULL,   \nOutTAqiPm10_3hr_minRT TIME NULL,\nOutAqiPm10_3hr_maxRT DECIMAL (5,1) NULL, \nOutTAqiPm10_3hr_maxRT TIME NULL,\nOutAqiPm10_24hr_avgRT DECIMAL (5,1) NULL,         \nOutAqiPm10_24hr_minRT DECIMAL (5,1) NULL, \nOutTAqiPm10_24hr_minRT TIME NULL,\nOutAqiPm10_24hr_maxRT DECIMAL (5,1) NULL, \nOutTAqiPm10_24hr_maxRT TIME NULL\n) AS (\n\nSELECT DATE(Realtime.LogDateTime) AS LogDateRT, \nag.OutPm1_avgRT AS OutPm1_avgRT, \nROUND(MIN(a1.pm1),1) AS OutPm1_minRT, a1.OutTPm1_minRT, \nROUND(MAX(b1.pm1),1) AS OutPm1_maxRT, b1.OutTPm1_maxRT,\n\nag.OutPm2p5_avgRT AS OutPm2p5_avgRT, \nROUND(MIN(c1.pm2p5),1) AS OutPm2p5_minRT, c1.OutTPm2p5_minRT, \nROUND(MAX(d1.pm2p5),1) AS OutPm2p5_maxRT, d1.OutTPm2p5_maxRT,\n\nag.OutPm2p5_1hr_avgRT AS OutPm2p5_1hr_avgRT, \nROUND(MIN(e1.pm2p5_1hr),1) AS OutPm2p5_1hr_minRT, e1.OutTPm2p5_1hr_minRT, \nROUND(MAX(f1.pm2p5_1hr),1) AS OutPm2p5_1hr_maxRT, f1.OutTpm2p5_1hr_maxRT,\n\nag.OutPm2p5_3hr_avgRT AS OutPm2p5_3hr_avgRT, \nROUND(MIN(g1.pm2p5_3hr),1) AS OutPm2p5_3hr_minRT, g1.OutTPm2p5_3hr_minRT, \nROUND(MAX(h1.pm2p5_3hr),1) AS OutPm2p5_3hr_maxRT, h1.OutTPm2p5_3hr_maxRT,\n\nag.OutPm2p5_24hr_avgRT AS OutPm2p5_24hr_avgRT, \nROUND(MIN(i1.pm2p5_24hr),1) AS OutPm2p5_24hr_minRT, i1.OutTPm2p5_24hr_minRT, \nROUND(MAX(j1.pm2p5_24hr),1) AS OutPm2p5_24hr_maxRT, j1.OutTPm2p5_24hr_maxRT,\n\nag.OutPm10_avgRT AS OutPm10_avgRT, \nROUND(MIN(k1.pm10),1) AS OutPm10_minRT, k1.OutTPm10_minRT, \nROUND(MAX(l1.pm10),1) AS OutPm10_maxRT, l1.OutTPm10_maxRT,\n\nag.OutPm10_1hr_avgRT AS OutPm10_1hr_avgRT, \nROUND(MIN(m1.pm10_1hr),1) AS OutPm10_1hr_minRT, m1.OutTPm10_1hr_minRT,\nROUND(MAX(n1.pm10_1hr),1) AS OutPm10_1hr_maxRT, n1.OutTPm10_1hr_maxRT,\n\nag.OutPm10_3hr_avgRT AS OutPm10_3hr_avgRT, \nROUND(MIN(o1.pm10_3hr),1) AS OutPm10_3hr_minRT, o1.OutTPm10_3hr_minRT, \nROUND(MAX(p1.pm10_3hr),1) AS OutPm10_3hr_maxRT, p1.OutTPm10_3hr_maxRT,\n\nag.OutPm10_24hr_avgRT AS OutPm10_24hr_avgRT, \nROUND(MIN(q1.pm10_24hr),1) AS OutPm10_24hr_minRT, q1.OutTPm10_24hr_minRT, \nROUND(MAX(r1.pm10_24hr),1) AS OutPm10_24hr_maxRT, r1.OutTPm10_24hr_maxRT,\n\nag.OutAqiPm2p5_avgRT AS OutAqiPm2p5_avgRT, \nROUND(MIN(a2.AQIpm2p5),1) AS OutAqiPm2p5_minRT, a2.OutTAqiPm2p5_minRT, \nROUND(MAX(b2.AQIpm2p5),1) AS OutAqiPm2p5_maxRT, b2.OutTAqiPm2p5_maxRT,\n\nag.OutAqiPm2p5_1hr_avgRT AS OutAqiPm2p5_1hr_avgRT, \nROUND(MIN(c2.AQIpm2p5_1hr),1) AS OutAqiPm2p5_1hr_minRT, c2.OutTAqiPm2p5_1hr_minRT, \nROUND(MAX(d2.AQIpm2p5_1hr),1) AS OutAqiPm2p5_1hr_maxRT, d2.OutTAqiPm2p5_1hr_maxRT,\n\nag.OutAqiPm2p5_3hr_avgRT AS OutAqiPm2p5_3hr_avgRT, \nROUND(MIN(e2.AQIpm2p5_3hr),1) AS OutAqiPm2p5_3hr_minRT, e2.OutTAqiPm2p5_3hr_minRT, \nROUND(MAX(f2.AQIpm2p5_3hr),1) AS OutAqiPm2p5_3hr_maxRT, f2.OutTAqiPm2p5_3hr_maxRT,\n\nag.OutAqiPm2p5_24hr_avgRT AS OutAqiPm2p5_24hr_avgRT, \nROUND(MIN(g2.AQIpm2p5_24hr),1) AS OutAqiPm2p5_24hr_minRT, g2.OutTAqiPm2p5_24hr_minRT, \nROUND(MAX(h2.AQIpm2p5_24hr),1) AS OutAqiPm2p5_24hr_maxRT, h2.OutTAqiPm2p5_24hr_maxRT,\n\nag.OutAqiPm10_avgRT AS OutAqiPm10_avgRT, \nROUND(MIN(i2.AQIpm10),1) AS OutAqiPm10_minRT, i2.OutTAqiPm10_minRT, \nROUND(MAX(j2.AQIpm10),1) AS OutAqiPm10_maxRT, j2.OutTAqiPm10_maxRT,\n\nag.OutAqiPm10_1hr_avgRT AS OutAqiPm10_1hr_avgRT, \nROUND(MIN(k2.AQIpm10_1hr),1) AS OutAqiPm10_1hr_minRT, k2.OutTAqiPm10_1hr_minRT, \nROUND(MAX(l2.AQIpm10_1hr),1) AS OutAqiPm10_1hr_maxRT, l2.OutTAqiPm10_1hr_maxRT,\n\nag.OutAqiPm10_3hr_avgRT AS OutAqiPm10_3hr_avgRT, \nROUND(MIN(m2.AQIpm10_3hr),1) AS OutAqiPm10_3hr_minRT, m2.OutTAqiPm10_3hr_minRT, \nROUND(MAX(n2.AQIpm10_3hr),1) AS OutAqiPm10_3hr_maxRT, n2.OutTAqiPm10_3hr_maxRT,\n\nag.OutAqiPm10_24hr_avgRT AS OutAqiPm10_24hr_avgRT, \nROUND(MIN(o2.AQIpm10_24hr),1) AS OutAqiPm10_24hr_minRT, o2.OutTAqiPm10_24hr_minRT, \nROUND(MAX(p2.AQIpm10_24hr),1) AS OutAqiPm10_24hr_maxRT, p2.OutTAqiPm10_24hr_maxRT\n\nFROM Realtime\n\nINNER JOIN(\n\t-- average values\n                SELECT Realtime.LogDateTime AS LogDate, \n\t\tROUND(AVG(Realtime.pm1),1) AS OutPm1_avgRT,\n\t\tROUND(AVG(Realtime.pm2p5),1) AS OutPm2p5_avgRT,\n\t\tROUND(AVG(Realtime.pm2p5_1hr),1) AS OutPm2p5_1hr_avgRT,\n\t\tROUND(AVG(Realtime.pm2p5_3hr),1) AS OutPm2p5_3hr_avgRT,\n\t\tROUND(AVG(Realtime.pm2p5_24hr),1) AS OutPm2p5_24hr_avgRT,\n\t\tROUND(AVG(Realtime.pm10),1) AS OutPm10_avgRT,\n\t\tROUND(AVG(Realtime.pm10_1hr),1) AS OutPm10_1hr_avgRT,\n\t\tROUND(AVG(Realtime.pm10_3hr),1) AS OutPm10_3hr_avgRT,\n\t\tROUND(AVG(Realtime.pm10_24hr),1) AS OutPm10_24hr_avgRT,\n\t\t\n\t\tROUND(AVG(Realtime.AQIpm2p5),1) AS OutAqiPm2p5_avgRT,\n\t\tROUND(AVG(Realtime.AQIpm2p5_1hr),1) AS OutAqiPm2p5_1hr_avgRT,\n\t\tROUND(AVG(Realtime.AQIpm2p5_3hr),1) AS OutAqiPm2p5_3hr_avgRT,\n\t\tROUND(AVG(Realtime.AQIpm2p5_24hr),1) AS OutAqiPm2p5_24hr_avgRT,\n\t\tROUND(AVG(Realtime.AQIpm10),1) AS OutAqiPm10_avgRT,\n\t\tROUND(AVG(Realtime.AQIpm10_1hr),1) AS OutAqiPm10_1hr_avgRT,\n\t\tROUND(AVG(Realtime.AQIpm10_3hr),1) AS OutAqiPm10_3hr_avgRT,\n\t\tROUND(AVG(Realtime.AQIpm10_24hr),1) AS OutAqiPm10_24hr_avgRT\n\tFROM Realtime\n    WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\tGROUP BY DAY(Realtime.LogDateTime), MONTH(Realtime.LogDateTime), YEAR(Realtime.LogDateTime)\n\t) ag ON DATE(Realtime.LogDateTime) = DATE(ag.LogDate)\n\n-- a1:  Pm1_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm1_minRT, pm1  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm1 ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()  \n\t) t\n\tWHERE t.rn = 1 )\na1 ON (Realtime.LogDateTime) = (a1.LogDate)\n\n-- b1: Pm1_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm1_maxRT, pm1  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm1 DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nb1 ON DATE(Realtime.LogDateTime) = DATE(b1.LogDate)\n\n-- c1:  Pm2p5_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm2p5_minRT, pm2p5  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5 ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nc1 ON DATE(Realtime.LogDateTime) = DATE(c1.LogDate)\n\n-- d1: Pm2p5_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm2p5_maxRT, pm2p5  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5 DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nd1 ON DATE(Realtime.LogDateTime) = DATE(d1.LogDate)\n\n-- e1:  Pm2p5_1hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_1hr_minRT, pm2p5_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_1hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ne1 ON DATE(Realtime.LogDateTime) = DATE(e1.LogDate)\n\n-- f1: Pm2p5_1hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_1hr_maxRT, pm2p5_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_1hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nf1 ON DATE(Realtime.LogDateTime) = DATE(f1.LogDate)\n\n-- g1:  Pm2p5_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_3hr_minRT, pm2p5_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_3hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ng1 ON DATE(Realtime.LogDateTime) = DATE(g1.LogDate)\n\n-- h1: Pm2p5_1hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_3hr_maxRT, pm2p5_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_3hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nh1 ON DATE(Realtime.LogDateTime) = DATE(h1.LogDate)\n\n-- i1:  Pm2p5_24hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_24hr_minRT, pm2p5_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_24hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ni1 ON DATE(Realtime.LogDateTime) = DATE(i1.LogDate)\n\n-- j1: Pm2p5_24hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_24hr_maxRT, pm2p5_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm2p5_24hr DESC, LogDateTime) rn\n\t  FROM Realtime \n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nj1 ON DATE(Realtime.LogDateTime) = DATE(j1.LogDate)\n\n-- k1:  Pm10_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm10_minRT, pm10  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10 ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nk1 ON DATE(Realtime.LogDateTime) = DATE(k1.LogDate)\n\n-- l1: Pm10_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm10_maxRT, pm10  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10 DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nl1 ON DATE(Realtime.LogDateTime) = DATE(l1.LogDate)\n\n-- m1:  OutTpm10_1hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_1hr_minRT, pm10_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_1hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nm1 ON DATE(Realtime.LogDateTime) = DATE(m1.LogDate)\n\n-- n1: OutTpm10_1hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_1hr_maxRT, pm10_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_1hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nn1 ON DATE(Realtime.LogDateTime) = DATE(n1.LogDate)\n\n-- o1:  OutTpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_3hr_minRT, pm10_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_3hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE() \n\t) t\n\tWHERE t.rn = 1 )\no1 ON DATE(Realtime.LogDateTime) = DATE(o1.LogDate)\n\n-- p1: OutTpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_3hr_maxRT, pm10_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_3hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE() \n\t) t\n\tWHERE t.rn = 1 )\np1 ON DATE(Realtime.LogDateTime) = DATE(p1.LogDate)\n\n-- q1:  OutTpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_24hr_minRT, pm10_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_24hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nq1 ON DATE(Realtime.LogDateTime) = DATE(q1.LogDate)\n\n-- r1: OutTpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_24hr_maxRT, pm10_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY pm10_24hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nr1 ON DATE(Realtime.LogDateTime) = DATE(r1.LogDate)\n\n\n-- a2:  AQIpm2p5_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_minRT, AQIpm2p5  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5 ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\na2 ON DATE(Realtime.LogDateTime) = DATE(a2.LogDate)\n\n-- b2: AQIm2p5_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_maxRT, AQIpm2p5  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5 DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nb2 ON DATE(Realtime.LogDateTime) = DATE(b2.LogDate)\n\n-- c2:  AQIpm2p5_1hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_1hr_minRT, AQIpm2p5_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_1hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nc2 ON DATE(Realtime.LogDateTime) = DATE(c2.LogDate)\n\n-- d2: AQIpm2p5_1hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_1hr_maxRT, AQIpm2p5_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_1hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nd2 ON DATE(Realtime.LogDateTime) = DATE(d2.LogDate)\n\n-- e2:  AQIpm2p5_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_3hr_minRT, AQIpm2p5_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_3hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ne2 ON DATE(Realtime.LogDateTime) = DATE(e2.LogDate)\n\n-- f2: AQIpm2p5_3hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_3hr_maxRT, AQIpm2p5_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_3hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nf2 ON DATE(Realtime.LogDateTime) = DATE(f2.LogDate)\n\n-- g2:  AQIpm2p5_24hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_24hr_minRT, AQIpm2p5_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_24hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ng2 ON DATE(Realtime.LogDateTime) = DATE(g2.LogDate)\n\n-- h2: AQIpm2p5_24hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_24hr_maxRT, AQIpm2p5_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm2p5_24hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nh2 ON DATE(Realtime.LogDateTime) = DATE(h2.LogDate)\n\n-- i2:  AQIpm10_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_minRT, AQIpm10 \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10 ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ni2 ON DATE(Realtime.LogDateTime) = DATE(i2.LogDate)\n\n-- j2: AQIpm10_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_maxRT, AQIpm10  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10 DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nj2 ON DATE(Realtime.LogDateTime) = DATE(j2.LogDate)\n\n-- k2:  AQIpm10_1hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_1hr_minRT, AQIpm10_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_1hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nk2 ON DATE(Realtime.LogDateTime) = DATE(k2.LogDate)\n\n-- l2: AQIpm10_1hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_1hr_maxRT, AQIpm10_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_1hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nl2 ON DATE(Realtime.LogDateTime) = DATE(l2.LogDate)\n\n-- m2:  AQIpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_3hr_minRT, AQIpm10_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_3hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nm2 ON DATE(Realtime.LogDateTime) = DATE(m2.LogDate)\n\n-- n2:  AQIpm10_3hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_3hr_maxRT, AQIpm10_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_3hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nn2 ON DATE(Realtime.LogDateTime) = DATE(n2.LogDate)\n\n\n-- o2: AQIpm10_24hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_24hr_minRT, AQIpm10_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_24hr ASC, LogDateTime) rn\n\t  FROM Realtime\n\t WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\no2 ON DATE(Realtime.LogDateTime) = DATE(o2.LogDate)\n\n-- p2: AQIpm10_24hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_24hr_maxRT, AQIpm10_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY AQIpm10_24hr DESC, LogDateTime) rn\n\t  FROM Realtime\n\t  WHERE DATE(Realtime.LogDateTime) > @lastUpdated AND DATE(Realtime.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\np2 ON DATE(Realtime.LogDateTime) = DATE(p2.LogDate)\n\n);\n\n\n-- values from monthly table\nDROP TEMPORARY TABLE IF EXISTS monthlyData;\nCREATE TEMPORARY TABLE IF NOT EXISTS monthlyData (\nLogDate DATE NOT NULL,          \nOutPm1_avg DECIMAL (5,1) NULL,          \nOutPm1_min DECIMAL (5,1) NULL,            \nOutTPm1_min TIME NULL,         \nOutPm1_max DECIMAL (5,1) NULL,           \nOutTPm1_max TIME NULL,       \nOutPm2p5_avg DECIMAL (5,1) NULL,         \nOutPm2p5_min DECIMAL (5,1) NULL,        \nOutTPm2p5_min TIME NULL,       \nOutPm2p5_max DECIMAL (5,1) NULL,        \nOutTPm2p5_max TIME NULL,     \nOutPm2p5_1hr_avg DECIMAL (5,1) NULL,     \nOutPm2p5_1hr_min DECIMAL (5,1) NULL,    \nOutTpm2p5_1hr_min TIME NULL,  \nOutPm2p5_1hr_max DECIMAL (5,1) NULL,    \nOutTpm2p5_1hr_max TIME NULL,  \nOutPm2p5_3hr_avg DECIMAL (5,1) NULL,    \nOutPm2p5_3hr_min DECIMAL (5,1) NULL,    \nOutTpm2p5_3hr_min TIME NULL,\nOutPm2p5_3hr_max DECIMAL (5,1) NULL,    \nOutTpm2p5_3hr_max TIME NULL,  \nOutPm2p5_24hr_avg DECIMAL (5,1) NULL,   \nOutPm2p5_24hr_min DECIMAL (5,1) NULL,    \nOutTpm2p5_24hr_min TIME NULL,  \nOutPm2p5_24hr_max DECIMAL (5,1) NULL,    \nOutTpm2p5_24hr_max TIME NULL, \nOutPm10_avg DECIMAL (5,1) NULL,           \nOutPm10_min DECIMAL (5,1) NULL,          \nOutTPm10_min TIME NULL,   \nOutPm10_max DECIMAL (5,1) NULL,         \nOutTPm10_max TIME NULL,      \nOutPm10_1hr_avg DECIMAL (5,1) NULL,    \nOutPm10_1hr_min DECIMAL (5,1) NULL,   \nOutTpm10_1hr_min TIME NULL,  \nOutPm10_1hr_max DECIMAL (5,1) NULL,      \nOutTpm10_1hr_max TIME NULL,   \nOutPm10_3hr_avg DECIMAL (5,1) NULL,     \nOutPm10_3hr_min DECIMAL (5,1) NULL,     \nOutTpm10_3hr_min TIME NULL,   \nOutPm10_3hr_max DECIMAL (5,1) NULL,     \nOutTpm10_3hr_max TIME NULL,    \nOutPm10_24hr_avg DECIMAL (5,1) NULL,    \nOutPm10_24hr_min DECIMAL (5,1) NULL,   \nOutTpm10_24hr_min TIME NULL,   \nOutPm10_24hr_max DECIMAL (5,1) NULL,   \nOutTpm10_24hr_max TIME NULL,  \nOutAqiPm2p5_avg DECIMAL (5,1) NULL,             \nOutAqiPm2p5_min DECIMAL (5,1) NULL, \nOutTAqiPm2p5_min TIME NULL,  \nOutAqiPm2p5_max DECIMAL (5,1) NULL,     \nOutTAqiPm2p5_max TIME NULL,    \nOutAqiPm2p5_1hr_avg DECIMAL (5,1) NULL,          \nOutAqiPm2p5_1hr_min DECIMAL (5,1) NULL, \nOutTAqiPm2p5_1hr_min TIME NULL,\nOutAqiPm2p5_1hr_max DECIMAL (5,1) NULL, \nOutTAqiPm2p5_1hr_max TIME NULL,\nOutAqiPm2p5_3hr_avg DECIMAL (5,1) NULL,          \nOutAqiPm2p5_3hr_min DECIMAL (5,1) NULL,  \nOutTAqiPm2p5_3hr_min TIME NULL,\nOutAqiPm2p5_3hr_max DECIMAL (5,1) NULL,\nOutTAqiPm2p5_3hr_max TIME NULL,\nOutAqiPm2p5_24hr_avg DECIMAL (5,1) NULL,          \nOutAqiPm2p5_24hr_min DECIMAL (5,1) NULL,\nOutTAqiPm2p5_24hr_min TIME NULL,\nOutAqiPm2p5_24hr_max DECIMAL (5,1) NULL,\nOutTAqiPm2p5_24hr_max TIME NULL,\nOutAqiPm10_avg DECIMAL (5,1) NULL,            \nOutAqiPm10_min DECIMAL (5,1) NULL,    \nOutTAqiPm10_min TIME NULL,     \nOutAqiPm10_max DECIMAL (5,1) NULL,     \nOutTAqiPm10_max TIME NULL,     \nOutAqiPm10_1hr_avg DECIMAL (5,1) NULL,           \nOutAqiPm10_1hr_min DECIMAL (5,1) NULL,   \nOutTAqiPm10_1hr_min TIME NULL,\nOutAqiPm10_1hr_max DECIMAL (5,1) NULL, \nOutTAqiPm10_1hr_max TIME NULL,\nOutAqiPm10_3hr_avg DECIMAL (5,1) NULL,        \nOutAqiPm10_3hr_min DECIMAL (5,1) NULL,   \nOutTAqiPm10_3hr_min TIME NULL,\nOutAqiPm10_3hr_max DECIMAL (5,1) NULL, \nOutTAqiPm10_3hr_max TIME NULL,\nOutAqiPm10_24hr_avg DECIMAL (5,1) NULL,         \nOutAqiPm10_24hr_min DECIMAL (5,1) NULL, \nOutTAqiPm10_24hr_min TIME NULL,\nOutAqiPm10_24hr_max DECIMAL (5,1) NULL, \nOutTAqiPm10_24hr_max TIME NULL\n) AS (\n\nSELECT DATE(MonthlyAirQual.LogDateTime) AS LogDate, \nag.OutPm1_avg AS OutPm1_avg, \nROUND(MIN(a1.OutPm1),1) AS OutPm1_min, a1.OutTPm1_min, \nROUND(MAX(b1.OutPm1),1) AS OutPm1_max, b1.OutTPm1_max,\n\nag.OutPm2p5_avg AS OutPm2p5_avg, \nROUND(MIN(c1.OutPm2p5),1) AS OutPm2p5_min, c1.OutTPm2p5_min, \nROUND(MAX(d1.OutPm2p5),1) AS OutPm2p5_max, d1.OutTPm2p5_max,\n\nag.OutPm2p5_1hr_avg AS OutPm2p5_1hr_avg, \nROUND(MIN(e1.OutPm2p5_1hr),1) AS OutPm2p5_1hr_min, e1.OutTPm2p5_1hr_min, \nROUND(MAX(f1.OutPm2p5_1hr),1) AS OutPm2p5_1hr_max, f1.OutTpm2p5_1hr_max,\n\nag.OutPm2p5_3hr_avg AS OutPm2p5_3hr_avg, \nROUND(MIN(g1.OutPm2p5_3hr),1) AS OutPm2p5_3hr_min, g1.OutTPm2p5_3hr_min, \nROUND(MAX(h1.OutPm2p5_3hr),1) AS OutPm2p5_3hr_max, h1.OutTPm2p5_3hr_max,\n\nag.OutPm2p5_24hr_avg AS OutPm2p5_24hr_avg, \nROUND(MIN(i1.OutPm2p5_24hr),1) AS OutPm2p5_24hr_min, i1.OutTPm2p5_24hr_min, \nROUND(MAX(j1.OutPm2p5_24hr),1) AS OutPm2p5_24hr_max, j1.OutTPm2p5_24hr_max,\n\nag.OutPm10_avg AS OutPm10_avg, \nROUND(MIN(k1.OutPm10),1) AS OutPm10_min, k1.OutTPm10_min, \nROUND(MAX(l1.OutPm10),1) AS OutPm10_max, l1.OutTPm10_max,\n\nag.OutPm10_1hr_avg AS OutPm10_1hr_avg, \nROUND(MIN(m1.OutPm10_1hr),1) AS OutPm10_1hr_min, m1.OutTPm10_1hr_min,\nROUND(MAX(n1.OutPm10_1hr),1) AS OutPm10_1hr_max, n1.OutTPm10_1hr_max,\n\nag.OutPm10_3hr_avg AS OutPm10_3hr_avg, \nROUND(MIN(o1.OutPm10_3hr),1) AS OutPm10_3hr_min, o1.OutTPm10_3hr_min, \nROUND(MAX(p1.OutPm10_3hr),1) AS OutPm10_3hr_max, p1.OutTPm10_3hr_max,\n\nag.OutPm10_24hr_avg AS OutPm10_24hr_avg, \nROUND(MIN(q1.OutPm10_24hr),1) AS OutPm10_24hr_min, q1.OutTPm10_24hr_min, \nROUND(MAX(r1.OutPm10_24hr),1) AS OutPm10_24hr_max, r1.OutTPm10_24hr_max,\n\nag.OutAqiPm2p5_avg AS OutAqiPm2p5_avg, \nROUND(MIN(a2.OutAqiPm2p5),1) AS OutAqiPm2p5_min, a2.OutTAqiPm2p5_min, \nROUND(MAX(b2.OutAqiPm2p5),1) AS OutAqiPm2p5_max, b2.OutTAqiPm2p5_max,\n\nag.OutAqiPm2p5_1hr_avg AS OutAqiPm2p5_1hr_avg, \nROUND(MIN(c2.OutAqiPm2p5_1hr),1) AS OutAqiPm2p5_1hr_min, c2.OutTAqiPm2p5_1hr_min, \nROUND(MAX(d2.OutAqiPm2p5_1hr),1) AS OutAqiPm2p5_1hr_max, d2.OutTAqiPm2p5_1hr_max,\n\nag.OutAqiPm2p5_3hr_avg AS OutAqiPm2p5_3hr_avg, \nROUND(MIN(e2.OutAqiPm2p5_3hr),1) AS OutAqiPm2p5_3hr_min, e2.OutTAqiPm2p5_3hr_min, \nROUND(MAX(f2.OutAqiPm2p5_3hr),1) AS OutAqiPm2p5_3hr_max, f2.OutTAqiPm2p5_3hr_max,\n\nag.OutAqiPm2p5_24hr_avg AS OutAqiPm2p5_24hr_avg, \nROUND(MIN(g2.OutAqiPm2p5_24hr),1) AS OutAqiPm2p5_24hr_min, g2.OutTAqiPm2p5_24hr_min, \nROUND(MAX(h2.OutAqiPm2p5_24hr),1) AS OutAqiPm2p5_24hr_max, h2.OutTAqiPm2p5_24hr_max,\n\nag.OutAqiPm10_avg AS OutAqiPm10_avg, \nROUND(MIN(i2.OutAqiPm10),1) AS OutAqiPm10_min, i2.OutTAqiPm10_min, \nROUND(MAX(j2.OutAqiPm10),1) AS OutAqiPm10_max, j2.OutTAqiPm10_max,\n\nag.OutAqiPm10_1hr_avg AS OutAqiPm10_1hr_avg, \nROUND(MIN(k2.OutAqiPm10_1hr),1) AS OutAqiPm10_1hr_min, k2.OutTAqiPm10_1hr_min, \nROUND(MAX(l2.OutAqiPm10_1hr),1) AS OutAqiPm10_1hr_max, l2.OutTAqiPm10_1hr_max,\n\nag.OutAqiPm10_3hr_avg AS OutAqiPm10_3hr_avg, \nROUND(MIN(m2.OutAqiPm10_3hr),1) AS OutAqiPm10_3hr_min, m2.OutTAqiPm10_3hr_min, \nROUND(MAX(n2.OutAqiPm10_3hr),1) AS OutAqiPm10_3hr_max, n2.OutTAqiPm10_3hr_max,\n\nag.OutAqiPm10_24hr_avg AS OutAqiPm10_24hr_avg, \nROUND(MIN(o2.OutAqiPm10_24hr),1) AS OutAqiPm10_24hr_min, o2.OutTAqiPm10_24hr_min, \nROUND(MAX(p2.OutAqiPm10_24hr),1) AS OutAqiPm10_24hr_max, p2.OutTAqiPm10_24hr_max\n\nFROM MonthlyAirQual\n\n\nINNER JOIN(\n\t-- average values\n                SELECT MonthlyAirQual.LogDateTime AS LogDate, \n\t\tROUND(AVG(MonthlyAirQual.OutPm1),1) AS OutPm1_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutPm2p5),1) AS OutPm2p5_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutPm2p5_1hr),1) AS OutPm2p5_1hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutPm2p5_3hr),1) AS OutPm2p5_3hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutPm2p5_24hr),1) AS OutPm2p5_24hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutPm10),1) AS OutPm10_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutPm10_1hr),1) AS OutPm10_1hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutPm10_3hr),1) AS OutPm10_3hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutPm10_24hr),1) AS OutPm10_24hr_avg,\n\t\t\n\t\tROUND(AVG(MonthlyAirQual.OutAqiPm2p5),1) AS OutAqiPm2p5_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutAqiPm2p5_1hr),1) AS OutAqiPm2p5_1hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutAqiPm2p5_3hr),1) AS OutAqiPm2p5_3hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutAqiPm2p5_24hr),1) AS OutAqiPm2p5_24hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutAqiPm10),1) AS OutAqiPm10_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutAqiPm10_1hr),1) AS OutAqiPm10_1hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutAqiPm10_3hr),1) AS OutAqiPm10_3hr_avg,\n\t\tROUND(AVG(MonthlyAirQual.OutAqiPm10_24hr),1) AS OutAqiPm10_24hr_avg\n\tFROM MonthlyAirQual\n    WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\tGROUP BY DAY(MonthlyAirQual.LogDateTime), MONTH(MonthlyAirQual.LogDateTime), YEAR(MonthlyAirQual.LogDateTime)\n\t) ag ON DATE(MonthlyAirQual.LogDateTime) = DATE(ag.LogDate)\n\n-- a1:  Pm1_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm1_min, OutPm1  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm1 ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\na1 ON (MonthlyAirQual.LogDateTime) = (a1.LogDate)\n\n-- b1: Pm1_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm1_max, OutPm1  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm1 DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nb1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(b1.LogDate)\n\n-- c1:  Pm2p5_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm2p5_min, OutPm2p5  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5 ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nc1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(c1.LogDate)\n\n-- d1: Pm2p5_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm2p5_max, OutPm2p5  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5 DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nd1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(d1.LogDate)\n\n-- e1:  Pm2p5_1hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_1hr_min, OutPm2p5_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_1hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ne1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(e1.LogDate)\n\n-- f1: Pm2p5_1hr_maxRT\nINNER JOIN(\n\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_1hr_max, OutPm2p5_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_1hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nf1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(f1.LogDate)\n\n-- g1:  Pm2p5_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_3hr_min, OutPm2p5_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_3hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ng1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(g1.LogDate)\n\n-- h1: Pm2p5_1hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_3hr_max, OutPm2p5_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_3hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nh1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(h1.LogDate)\n\n-- i1:  Pm2p5_24hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_24hr_min, OutPm2p5_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_24hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ni1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(i1.LogDate)\n\n-- j1: Pm2p5_24hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm2p5_24hr_max, OutPm2p5_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm2p5_24hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual \n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nj1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(j1.LogDate)\n\n-- k1:  Pm10_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm10_min, OutPm10  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10 ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nk1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(k1.LogDate)\n\n-- l1: Pm10_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTPm10_max, OutPm10  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10 DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nl1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(l1.LogDate)\n\n-- m1:  OutTpm10_1hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_1hr_min, OutPm10_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_1hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nm1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(m1.LogDate)\n\n-- n1: OutTpm10_1hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_1hr_max, OutPm10_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_1hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nn1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(n1.LogDate)\n\n-- o1:  OutTpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_3hr_min, OutPm10_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_3hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\no1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(o1.LogDate)\n\n-- p1: OutTpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_3hr_max, OutPm10_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_3hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\np1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(p1.LogDate)\n\n-- q1:  OutTpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_24hr_min, OutPm10_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_24hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nq1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(q1.LogDate)\n\n-- r1: OutTpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTpm10_24hr_max, OutPm10_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutPm10_24hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nr1 ON DATE(MonthlyAirQual.LogDateTime) = DATE(r1.LogDate)\n\n\n-- a2:  AQIpm2p5_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_min, OutAqiPm2p5  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5 ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\na2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(a2.LogDate)\n\n-- b2: AQIm2p5_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_max, OutAqiPm2p5  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5 DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE() \n\t) t\n\tWHERE t.rn = 1 )\nb2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(b2.LogDate)\n\n-- c2:  AQIpm2p5_1hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_1hr_min, OutAqiPm2p5_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_1hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nc2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(c2.LogDate)\n\n-- d2: AQIpm2p5_1hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_1hr_max, OutAqiPm2p5_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_1hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nd2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(d2.LogDate)\n\n-- e2:  AQIpm2p5_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_3hr_min, OutAqiPm2p5_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_3hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ne2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(e2.LogDate)\n\n-- f2: AQIpm2p5_3hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_3hr_max, OutAqiPm2p5_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_3hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nf2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(f2.LogDate)\n\n-- g2:  AQIpm2p5_24hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_24hr_min, OutAqiPm2p5_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_24hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE() \n\t) t\n\tWHERE t.rn = 1 )\ng2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(g2.LogDate)\n\n-- h2: AQIpm2p5_24hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm2p5_24hr_max, OutAqiPm2p5_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm2p5_24hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nh2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(h2.LogDate)\n\n-- i2:  AQIpm10_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_min, OutAqiPm10 \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10 ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\ni2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(i2.LogDate)\n\n-- j2: AQIpm10_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_max, OutAqiPm10  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10 DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE() \n\t) t\n\tWHERE t.rn = 1 )\nj2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(j2.LogDate)\n\n-- k2:  AQIpm10_1hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_1hr_min, OutAqiPm10_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_1hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nk2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(k2.LogDate)\n\n-- l2: AQIpm10_1hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_1hr_max, OutAqiPm10_1hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_1hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE() \n\t) t\n\tWHERE t.rn = 1 )\nl2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(l2.LogDate)\n\n-- m2:  AQIpm10_3hr_minRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_3hr_min, OutAqiPm10_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_3hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nm2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(m2.LogDate)\n\n-- n2:  AQIpm10_3hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_3hr_max, OutAqiPm10_3hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_3hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\nn2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(n2.LogDate)\n\n\n-- o2: AQIpm10_24hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_24hr_min, OutAqiPm10_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_24hr ASC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\no2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(o2.LogDate)\n\n-- p2: AQIpm10_24hr_maxRT\nINNER JOIN(\n\tSELECT (LogDateTime) AS LogDate, TIME_FORMAT(LogDateTime,\"%H:%i\") AS OutTAqiPm10_24hr_max, OutAqiPm10_24hr  \n\tFROM (\n\t  SELECT *, \n\t    ROW_NUMBER() OVER (PARTITION BY DATE(LogDateTime) ORDER BY OutAqiPm10_24hr DESC, LogDateTime) rn\n\t  FROM MonthlyAirQual\n\t  WHERE DATE(MonthlyAirQual.LogDateTime) > @lastUpdated AND DATE(MonthlyAirQual.LogDateTime) < CURDATE()\n\t) t\n\tWHERE t.rn = 1 )\np2 ON DATE(MonthlyAirQual.LogDateTime) = DATE(p2.LogDate)\n    \nGROUP BY DAY(MonthlyAirQual.LogDateTime), MONTH(MonthlyAirQual.LogDateTime), YEAR(MonthlyAirQual.LogDateTime)\n\n);\n\n-- insert data from temporary tables\nINSERT IGNORE INTO DailyAirQual (\n       \nLogDate,          \nOutPm1_avg,          \nOutPm1_min,            \nOutTPm1_min,         \nOutPm1_max,           \nOutTPm1_max,       \nOutPm2p5_avg,         \nOutPm2p5_min,        \nOutTPm2p5_min,       \nOutPm2p5_max,        \nOutTPm2p5_max,     \nOutPm2p5_1hr_avg,     \nOutPm2p5_1hr_min,    \nOutTpm2p5_1hr_min,  \nOutPm2p5_1hr_max,    \nOutTpm2p5_1hr_max,  \nOutPm2p5_3hr_avg,    \nOutPm2p5_3hr_min,    \nOutTpm2p5_3hr_min,\nOutPm2p5_3hr_max,    \nOutTpm2p5_3hr_max,  \nOutPm2p5_24hr_avg,   \nOutPm2p5_24hr_min,    \nOutTpm2p5_24hr_min,  \nOutPm2p5_24hr_max,    \nOutTpm2p5_24hr_max, \nOutPm10_avg,           \nOutPm10_min,          \nOutTPm10_min,   \nOutPm10_max,         \nOutTPm10_max,      \nOutPm10_1hr_avg,    \nOutPm10_1hr_min,   \nOutTpm10_1hr_min,  \nOutPm10_1hr_max,      \nOutTpm10_1hr_max,   \nOutPm10_3hr_avg,     \nOutPm10_3hr_min,     \nOutTpm10_3hr_min,   \nOutPm10_3hr_max,     \nOutTpm10_3hr_max,    \nOutPm10_24hr_avg,    \nOutPm10_24hr_min,   \nOutTpm10_24hr_min,   \nOutPm10_24hr_max,   \nOutTpm10_24hr_max,  \nOutAqiPm2p5_avg,             \nOutAqiPm2p5_min, \nOutTAqiPm2p5_min,  \nOutAqiPm2p5_max,     \nOutTAqiPm2p5_max,    \nOutAqiPm2p5_1hr_avg,          \nOutAqiPm2p5_1hr_min, \nOutTAqiPm2p5_1hr_min,\nOutAqiPm2p5_1hr_max, \nOutTAqiPm2p5_1hr_max,\nOutAqiPm2p5_3hr_avg,          \nOutAqiPm2p5_3hr_min,  \nOutTAqiPm2p5_3hr_min,\nOutAqiPm2p5_3hr_max,\nOutTAqiPm2p5_3hr_max,\nOutAqiPm2p5_24hr_avg,          \nOutAqiPm2p5_24hr_min,\nOutTAqiPm2p5_24hr_min,\nOutAqiPm2p5_24hr_max,\nOutTAqiPm2p5_24hr_max,\nOutAqiPm10_avg,            \nOutAqiPm10_min,    \nOutTAqiPm10_min,     \nOutAqiPm10_max,     \nOutTAqiPm10_max,     \nOutAqiPm10_1hr_avg,           \nOutAqiPm10_1hr_min,   \nOutTAqiPm10_1hr_min,\nOutAqiPm10_1hr_max, \nOutTAqiPm10_1hr_max,\nOutAqiPm10_3hr_avg,        \nOutAqiPm10_3hr_min,   \nOutTAqiPm10_3hr_min,\nOutAqiPm10_3hr_max, \nOutTAqiPm10_3hr_max,\nOutAqiPm10_24hr_avg,         \nOutAqiPm10_24hr_min, \nOutTAqiPm10_24hr_min,\nOutAqiPm10_24hr_max, \nOutTAqiPm10_24hr_max\n\n)\n-- use GREATEST and COALESCE to return the highest non null value\nSELECT DATE(monthlyData.LogDate) AS LogDate,\nROUND(GREATEST(COALESCE(rtData.OutPm1_avgRT,0),monthlyData.OutPm1_avg),1) AS OutPm1_avg,\nROUND(GREATEST(COALESCE(rtData.OutPm1_minRT,0),monthlyData.OutPm1_min),1) AS OutPm1_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm1_maxRT,0),monthlyData.OutPm1_max),1) AS OutPm1_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_avgRT,0),monthlyData.OutPm2p5_avg),1) AS OutPm2p5_avg,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_minRT,0),monthlyData.OutPm2p5_min),1) AS OutPm2p5_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_maxRT,0),monthlyData.OutPm2p5_max),1) AS OutPm2p5_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_1hr_avgRT,0),monthlyData.OutPm2p5_1hr_avg),1) AS OutPm2p5_1hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_1hr_minRT,0),monthlyData.OutPm2p5_1hr_min),1) AS OutPm2p5_1hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_1hr_maxRT,0),monthlyData.OutPm2p5_1hr_max),1) AS OutPm2p5_1hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_3hr_avgRT,0),monthlyData.OutPm2p5_3hr_avg),1) AS OutPm2p5_3hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_3hr_minRT,0),monthlyData.OutPm2p5_3hr_min),1) AS OutPm2p5_3hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_3hr_maxRT,0),monthlyData.OutPm2p5_3hr_max),1) AS OutPm2p5_3hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_24hr_avgRT,0),monthlyData.OutPm2p5_24hr_avg),1) AS OutPm2p5_24hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_24hr_minRT,0),monthlyData.OutPm2p5_24hr_min),1) AS OutPm2p5_24hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm2p5_24hr_maxRT,0),monthlyData.OutPm2p5_24hr_max),1) AS OutPm2p5_24hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm10_avgRT,0),monthlyData.OutPm10_avg),1) AS OutPm10_avg,\nROUND(GREATEST(COALESCE(rtData.OutPm10_minRT,0),monthlyData.OutPm10_min),1) AS OutPm10_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm10_maxRT,0),monthlyData.OutPm10_max),1) AS OutPm10_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm10_1hr_avgRT,0),monthlyData.OutPm10_1hr_avg),1) AS OutPm10_1hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutPm10_1hr_minRT,0),monthlyData.OutPm10_1hr_min),1) AS OutPm10_1hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm10_1hr_maxRT,0),monthlyData.OutPm10_1hr_max),1) AS OutPm10_1hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm10_3hr_avgRT,0),monthlyData.OutPm10_3hr_avg),1) AS OutPm10_3hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutPm10_3hr_minRT,0),monthlyData.OutPm10_3hr_min),1) AS OutPm10_3hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm10_3hr_maxRT,0),monthlyData.OutPm10_3hr_max),1) AS OutPm10_3hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm10_24hr_avgRT,0),monthlyData.OutPm10_24hr_avg),1) AS OutPm10_24hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutPm10_24hr_minRT,0),monthlyData.OutPm10_24hr_min),1) AS OutPm10_24hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutPm10_24hr_maxRT,0),monthlyData.OutPm10_24hr_max),1) AS OutPm10_24hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_avgRT,0),monthlyData.OutAqiPm2p5_avg),1) AS OutAqiPm2p5_avg,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_minRT,0),monthlyData.OutAqiPm2p5_min),1) AS OutAqiPm2p5_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_maxRT,0),monthlyData.OutAqiPm2p5_max),1) AS OutAqiPm2p5_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_1hr_avgRT,0),monthlyData.OutAqiPm2p5_1hr_avg),1) AS OutAqiPm2p5_1hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_1hr_minRT,0),monthlyData.OutAqiPm2p5_1hr_min),1) AS OutAqiPm2p5_1hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_1hr_maxRT,0),monthlyData.OutAqiPm2p5_1hr_max),1) AS OutAqiPm2p5_1hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_3hr_avgRT,0),monthlyData.OutAqiPm2p5_3hr_avg),1) AS OutAqiPm2p5_3hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_3hr_minRT,0),monthlyData.OutAqiPm2p5_3hr_min),1) AS OutAqiPm2p5_3hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_3hr_maxRT,0),monthlyData.OutAqiPm2p5_3hr_max),1) AS OutAqiPm2p5_3hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_24hr_avgRT,0),monthlyData.OutAqiPm2p5_24hr_avg),1) AS OutAqiPm2p5_24hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_24hr_minRT,0),monthlyData.OutAqiPm2p5_24hr_min),1) AS OutAqiPm2p5_24hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm2p5_24hr_maxRT,0),monthlyData.OutAqiPm2p5_24hr_max),1) AS OutAqiPm2p5_24hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_avgRT,0),monthlyData.OutAqiPm10_avg),1) AS OutAqiPm10_avg,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_minRT,0),monthlyData.OutAqiPm10_min),1) AS OutAqiPm10_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_maxRT,0),monthlyData.OutAqiPm10_max),1) AS OutAqiPm10_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_1hr_avgRT,0),monthlyData.OutAqiPm10_1hr_avg),1) AS OutAqiPm10_1hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_1hr_minRT,0),monthlyData.OutAqiPm10_1hr_min),1) AS OutAqiPm10_1hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_1hr_maxRT,0),monthlyData.OutAqiPm10_1hr_max),1) AS OutAqiPm10_1hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_3hr_avgRT,0),monthlyData.OutAqiPm10_3hr_avg),1) AS OutAqiPm10_3hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_3hr_minRT,0),monthlyData.OutAqiPm10_3hr_min),1) AS OutAqiPm10_3hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_3hr_maxRT,0),monthlyData.OutAqiPm10_3hr_max),1) AS OutAqiPm10_3hr_max,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_24hr_avgRT,0),monthlyData.OutAqiPm10_24hr_avg),1) AS OutAqiPm10_24hr_avg,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_24hr_minRT,0),monthlyData.OutAqiPm10_24hr_min),1) AS OutAqiPm10_24hr_min,\nCASE 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,\nROUND(GREATEST(COALESCE(rtData.OutAqiPm10_24hr_maxRT,0),monthlyData.OutAqiPm10_24hr_max),1) AS OutAqiPm10_24hr_max,\nCASE 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\nFROM rtData\n\n-- join the monthly data to the realtime data\nRIGHT JOIN monthlyData ON DAY(rtData.LogDateRT) = DAY(monthlyData.LogDate) AND MONTH(rtData.LogDateRT) = MONTH(monthlyData.LogDate) AND YEAR(rtData.LogDateRT) = YEAR(monthlyData.LogDate)\nGROUP BY DAY(monthlyData.LogDate), MONTH(monthlyData.LogDate), YEAR(monthlyData.LogDate);\n\nCOMMIT;\n\nEND$$\nDELIMITER ;<\/pre><\/div>\n\n\n\n
A PHP script contains SQL queries that retrieve the necessary information from this table and  are assigns to variables when the web page is visited. This allows for these statistics to be shown on the web page and is the same principle as several other statistics on these and other pages. The SQL queries are fairly simple because the daily air quality data already shows all the required summarised information for each day. <\/p>\n
\r\n    
\r\n    \r\n                        <\/i>\r\n                <\/a>\r\n    0    <\/span>\r\n<\/div>
\r\n    \r\n                        <\/i>\r\n                <\/a>\r\n    0<\/span>\r\n<\/div><\/div>","protected":false},"excerpt":{"rendered":"
The air quality data we recently began to measure has been expanded to include statistics for this month, this year and all-time.  This data is now available on the this month and year and all-time record pages. In the short period that we have been measuring air quality the air is exceptionally clean. For the […]<\/p>\n
<\/div>
Want to share this?<\/div>
<\/path><\/svg><\/span> <\/span><\/a><\/path><\/svg><\/span> <\/span><\/a><\/path><\/svg><\/span> <\/span><\/a><\/path><\/svg><\/span> <\/span><\/a><\/g><\/svg><\/span> <\/span><\/a><\/path><\/path><\/svg><\/span> <\/span><\/a><\/svg><\/span> <\/span><\/a> <\/span><\/path><\/g><\/svg><\/span><\/a> <\/span>
<\/div><\/a><\/div><\/div>
<\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5],"tags":[],"class_list":["post-231","post","type-post","status-publish","format-standard","hentry","category-announcements","category-web-development"],"_links":{"self":[{"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/posts\/231","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/comments?post=231"}],"version-history":[{"count":8,"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/posts\/231\/revisions"}],"predecessor-version":[{"id":241,"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/posts\/231\/revisions\/241"}],"wp:attachment":[{"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/media?parent=231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/categories?post=231"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fernygroveweather.com\/blog\/wp-json\/wp\/v2\/tags?post=231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}