CREATE VIEW vrequests AS
	SELECT
		r.*,
		COALESCE(fm_4s.ophours, fm_3s.ophours, fm_2s.ophours, fm_1s.ophours) as ophours
	FROM
		requests r
		LEFT JOIN forecast_mapping fm_4s
			ON (
				fm_4s.region_id = r.region_id
				AND fm_4s.job_category_id = r.job_category_id
				AND fm_4s.job_type_id = r.job_type_id
				AND fm_4s.zone_id = r.zone_id
			)
		LEFT JOIN forecast_mapping fm_3s
			ON (
				fm_3s.region_id = r.region_id
				AND fm_3s.job_category_id = r.job_category_id
				AND fm_3s.job_type_id = r.job_type_id
				AND fm_3s.zone_id IS NULL
			)
		LEFT JOIN forecast_mapping fm_2s
			ON (
				fm_2s.region_id = r.region_id
				AND fm_2s.job_category_id = r.job_category_id
				AND fm_2s.job_type_id IS NULL
				AND fm_2s.zone_id IS NULL
			)
		LEFT JOIN forecast_mapping fm_1s
			ON (
				fm_1s.region_id = r.region_id
				AND fm_1s.job_category_id IS NULL
				AND fm_1s.job_type_id IS NULL
				AND fm_1s.zone_id IS NULL
			);