
//WORKS!

SELECT 
CAST(MAX(CASE i_s.ice_surface_name WHEN "Ken Bracko" THEN t.ice_temp END) AS DECIMAL(3,1)) AS mb1_ice_temp, 
MAX(CASE i_s.ice_surface_name WHEN "Ken Bracko" THEN i_s.id END) AS ice_surface1_id, 
CAST(MAX(CASE i_s.ice_surface_name WHEN "Perry Cavanagh" THEN t.ice_temp END) AS DECIMAL(3,1)) AS mb2_ice_temp, 
MAX(CASE i_s.ice_surface_name WHEN "Perry Cavanagh" THEN i_s.id END) AS ice_surface2_id, 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.active END) AS "CM101 Active", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.runtime_hours END) AS "CM101 Hours", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.suction END) AS "CM101 Suction", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.discharge END) AS "CM101 Discharge", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.discharge_pressure END) AS "CM101 Discharge Pressure", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.oil_temperature END) AS "CM101 Oil Temperature", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.water_temperature END) AS "CM101 Water Temperature",
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.active END) AS "CM201 Active", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.runtime_hours END) AS "CM201 Hours", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.suction END) AS "CM201 Suction", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.discharge END) AS "CM201 Discharge", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.discharge_pressure END) AS "CM201 Discharge Pressure", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.oil_temperature END) AS "CM201 Oil Temperature", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.water_temperature END) AS "CM201 Water Temperature",
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.active END) AS "CM301 Active", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.runtime_hours END) AS "CM301 Hours", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.suction END) AS "CM301 Suction", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.discharge END) AS "CM301 Discharge", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.discharge_pressure END) AS "CM301 Discharge Pressure", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.oil_temperature END) AS "CM301 Oil Temperature", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.water_temperature END) AS "CM301 Water Temperature",
cmts.comment_text AS "Comment",
r.dolphin as "Dolphin",
r.hot_brine as "Hot Brine",
r.cold_brine as "Cold Brine",
r.made_by as "Made By"
FROM plant_readings r 
LEFT JOIN ice_temps t ON r.id = t.plant_readings_id 
LEFT JOIN ice_surfaces i_s ON t.ice_surface_id = i_s.id 
LEFT JOIN compressor_readings c_r ON c_r.plant_readings_id = r.id
LEFT JOIN compressors c ON c_r.compressor_id = c.id
LEFT JOIN comments cmts ON cmts.plant_readings_id = r.id
WHERE r.created_at BETWEEN "2024-04-01" AND "2024-05-06" GROUP BY r.id;

SELECT r.id,
r.created_at as "Date Time",
CAST(MAX(CASE i_s.ice_surface_name WHEN "Ken Bracko" THEN t.ice_temp END) AS DECIMAL(3,1)) AS mb1_ice_temp, 
CAST(MAX(CASE i_s.ice_surface_name WHEN "Perry Cavanagh" THEN t.ice_temp END) AS DECIMAL(3,1)) AS mb2_ice_temp, 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.active END) AS "CM101 Active", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.runtime_hours END) AS "CM101 Hours", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.suction END) AS "CM101 Suction", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.discharge END) AS "CM101 Discharge", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.discharge_pressure END) AS "CM101 Discharge Pressure", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.oil_temperature END) AS "CM101 Oil Temperature", 
MAX(CASE c.compressor_name WHEN 'CM101' THEN c_r.water_temperature END) AS "CM101 Water Temperature",
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.active END) AS "CM201 Active", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.runtime_hours END) AS "CM201 Hours", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.suction END) AS "CM201 Suction", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.discharge END) AS "CM201 Discharge", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.discharge_pressure END) AS "CM201 Discharge Pressure", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.oil_temperature END) AS "CM201 Oil Temperature", 
MAX(CASE c.compressor_name WHEN 'CM201' THEN c_r.water_temperature END) AS "CM201 Water Temperature",
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.active END) AS "CM301 Active", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.runtime_hours END) AS "CM301 Hours", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.suction END) AS "CM301 Suction", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.discharge END) AS "CM301 Discharge", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.discharge_pressure END) AS "CM301 Discharge Pressure", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.oil_temperature END) AS "CM301 Oil Temperature", 
MAX(CASE c.compressor_name WHEN 'CM301' THEN c_r.water_temperature END) AS "CM301 Water Temperature",
cmts.comment_text AS "Comment",
r.dolphin as "Dolphin",
r.hot_brine as "Hot Brine",
r.cold_brine as "Cold Brine",
r.made_by as "Made By"
FROM plant_readings r 
LEFT JOIN ice_temps t ON r.id = t.plant_readings_id 
LEFT JOIN ice_surfaces i_s ON t.ice_surface_id = i_s.id 
LEFT JOIN compressor_readings c_r ON c_r.plant_readings_id = r.id
LEFT JOIN compressors c ON c_r.compressor_id = c.id
LEFT JOIN comments cmts ON cmts.plant_readings_id = r.id
WHERE r.created_at BETWEEN "2024-04-01" AND "2024-05-06" GROUP BY r.id DESC;