CREATE VIEW vwproject AS
SELECT Year(a.start_date) , 
	   IF(type = '0', 'EE', IF(type = '1' , 'RE' , IF ( type = '2' , 'EE&RE' ,''))) as Group_C,
	   a.measure_id,
	   a.name,
	   c.name as objective,
	   d.name as detail,
	   e.name as department,
	   f.name as sector,
	   g.name as subsector,
	   a.start_date as start_d,
	   a.expected_end_date as end_d,
	   h.renewable_energy_id as re_energy_id,
	   i.name as re_energy,
	   a.supported_budget as budget,
	   a.target as target_amt,
	   a.target_unit as unit_c,
	   IF (a.target_unit = '1' , 'toe' , IF (a.target_unit = '2' , 'kWhr','')) unit_desc,
	   a.actual_mv as result_amt,
	   status
FROM project a
     LEFT JOIN measure b ON a.measure_id = b.code
	 LEFT JOIN project_objective c ON a.id = c.project_id
	 LEFT JOIN project_scope d ON a.id = c.project_id
	 LEFT JOIN bureau e ON a.bureau_id = e.id
	 LEFT JOIN sector f ON a.sector_id = f.code
	 LEFT JOIN subsector g ON a.sector_id = g.sector_id AND a.subsector_id = g.code
	 LEFT JOIN project_re h ON a.id = h.project_id
	 LEFT JOIN renewable_energy i ON h.renewable_energy_id = i.id
WHERE status = '100' AND b.deleted = 0