select downtime.*,department.department_nameEN,equipment.equipment_nameEN,
sum(corrective_time+cr_load_time)as total_d,
COUNT(downtime.department_id) AS countDE
from downtime
left join department on downtime.department_id=department.department_id
left join equipment on downtime.equipment_id=equipment.equipment_id
where downtime.plan='Unplan' and (downtime_date>='2015-10-20' and downtime_date<='2015-10-26')
group by downtime.department_id
ได้ละครับ แต่ติดตรง left join ตารางเพื่อดึงชื่อมาแสดงครับ
Code (SQL)
select nm,st,en,department.department_nameEN,
sum(corrective_time+cr_load_time)as dt,COUNT(department_id) as occ
from downtime tb1
left join department on downtime.department_id=department.department_id
inner join (
select 'curweek' nm, DATE_SUB('2015-10-26', interval 6 day) st, '2015-10-26' en union all
select 'retro1week', DATE_SUB('2015-10-26', interval 13 day), DATE_SUB('2015-10-26', interval 7 day) union all
select 'retro2week', DATE_SUB('2015-10-26', interval 20 day), DATE_SUB('2015-10-26', interval 14 day) union all
select 'retro3week', DATE_SUB('2015-10-26', interval 27 day), DATE_SUB('2015-10-26', interval 21 day)
) tb2
on tb1.downtime_date between tb2.st and tb2.en
where plan='Unplan' and department_id='10'
group by tb2.nm,tb1.department_id
Load balance : Server 03
Quote:
