/*
 * messwerte_metar2ready.sql
 * MeteoNews GmbH, Beat Vontobel, 2004-02-24
 *
 * SQL-Query to...
 *	transfer and convert data from metar to messwerte_ready
 *	compute pressure tendency ptend
 *
 * 2004-04-15  Bugfix: Added check for 'old.ppnn IS NULL' for cases
 *             where no last measurement row exists
 * 2004-09-29  Adapted from dbCopy to standalone SQL query
 *
 */

REPLACE INTO	ms.messwerte_ready
SELECT		stations.mexs_id AS mexs_id,
		SUBSTRING(NOW() + 0, 1, 10) AS datum,
		'metar' AS herkunft,
		IF(metar.dd IS NULL, -99, metar.dd) AS dd,
		IF(metar.ff IS NULL, -99, metar.ff) AS ff,
		IF(metar.fxx IS NULL, -99, metar.fxx) AS fxx,
		IF(metar.tt IS NULL, -99, metar.tt) AS tt,
		IF(metar.td IS NULL, -99, metar.td) AS td,
		NULL AS tc,
		IF(metar.rh IS NULL, -99, metar.rh) AS rh,
		IF(metar.ppnn IS NULL, -99, metar.ppnn) AS ppnn,
		IF(	/* if we don't have a new measurement we keep */
			/* the old value for ptend                    */
			metar.datetime = old.messzeit,
			old.ptend,
			/* else we check if we really have two valid  */
			/* measurements to compute tendency, if not   */
			/* we return -99                              */
			IF(	metar.ppnn IS NULL OR old.ppnn = -99 OR old.ppnn IS NULL,
				-99,
				/* we compute the pressure change     */
				/* normalized to the period of one    */
				/* hour                               */
				ROUND(	(metar.ppnn - old.ppnn)
					/ (UNIX_TIMESTAMP(metar.datetime) - UNIX_TIMESTAMP(old.messzeit))
					* 3600
				)
			)
		) AS ptend,
		-99 AS rrr,
		-99 AS ssd,
		wx AS ww,
		NULL AS bearbeitet,
		`datetime` AS messzeit
FROM		mn.metar
INNER JOIN	ms.stationen AS stations
ON		metar.id = stations.icao
LEFT JOIN	ms.messwerte_ready AS old
USING		(mexs_id)
WHERE		metar.datetime >= (NOW() - INTERVAL 3 HOUR)