select p.*, d.timetableSeq, d.data15 as data15Curr, d.pvPower as pvPowerCurr from ( select p.seq as plantSeq, p.name as plantName, p.supplyPower, min(d.data15) as data15Min, max(d.data15) as data15Max, min(d.data32) as data32, max(d.data33) as data33, min(d.data16) as data16, min(d.pvPower) as pvPowerMin, max(d.pvPower) as pvPowerMax, round(avg(d.pvPower)) as pvPowerAvg, min(d.data20) as data20Min, max(d.data20) as data20Max, min(d.data21) as data21Min, max(d.data21) as data21Max, min(d.data22) as data22Min, max(d.data22) as data22Max, min(d.data23) as data23Min, max(d.data23) as data23Max, max(d.timetableSeq) as timetableSeq, (select count(t.seq) from t_timetable_pms t where t.inputDate = #{startDate} ) as pmsCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate = #{startDate} ) as kescoCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate = #{startDate} and k.sendStatus = '00' ) as kescoFailCnt, (select count(m.seq) from t_pmsdata_2021 m where m.plantSeq = p.seq and m.pcsIdx = 0 and m.inputDate = #{startDate} and (m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0 or m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0) ) as pmsFailCnt from t_plant p, t_pmsdata_${searchYear} d where p.seq = d.plantSeq and d.inputDate = #{startDate} group by p.seq ) p, t_pmsdata_2021 d where p.plantSeq = d.plantSeq and d.inputDate = #{startDate} and d.timetableSeq = p.timetableSeq and d.pcsIdx = 0 order by p.plantSeq desc select p.*, count(m.seq) as pmsFailCnt from ( select p.seq as plantSeq, p.name as plantName, p.supplyPower, min(d.data15) as data15Min, max(d.data15) as data15Max, round(avg(d.data15)) as data15Avg, min(d.data32) as data32, max(d.data33) as data33, min(d.data16) as data16, min(d.pvPower) as pvPowerMin, max(d.pvPower) as pvPowerMax, round(avg(d.pvPower)) as pvPowerAvg, (select count(t.seq) from t_timetable_pms t where t.inputDate between #{startDate} and #{endDate} ) as pmsCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate between #{startDate} and #{endDate} ) as kescoCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate between #{startDate} and #{endDate} and k.sendStatus = '00' ) as kescoFailCnt from t_plant p, t_pmsdata_statistic_day d where p.seq = d.plantSeq and d.inputDate between #{startDate} and #{endDate} group by p.seq ) p left join t_pmsdata_${searchYear} m on p.plantSeq = m.plantSeq and m.inputDate between #{startDate} and #{endDate} and (m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0 or m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0) group by p.plantSeq select p.name as plantName, d.* from ( select plantSeq, CAST(SUM(CASE WHEN typeCode ='10' THEN modelCode END ) AS CHAR(2)) as device10, CAST(SUM(CASE WHEN typeCode ='20' THEN modelCode END ) AS CHAR(2)) as device20, CAST(SUM(CASE WHEN typeCode ='30' THEN modelCode END ) AS CHAR(2)) as device30, SUM(CASE WHEN typeCode ='30' THEN quantity ELSE 0 END ) as device30Quantity, CAST(SUM(CASE WHEN typeCode ='40' THEN modelCode END ) AS CHAR(2)) as device40, SUM(CASE WHEN typeCode ='40' THEN quantity ELSE 0 END ) as device40Quantity from t_device group by plantSeq ) d, t_plant p where d.plantSeq = p.seq and p.plantStatus='01'