select
a.tmh_program_date,a.tmh_tpcode,a.tmh_equitment_code,a.tmh_time
atime,a.tmh_hour a,c.tmh_time ctime,c.tmh_hour c,p1.tmh_time
p1time,p1.tmh_hour p1,p0.tmh_time p0time,p0.tmh_hour p0,
(c.tmh_hour-a.tmh_hour) ac,(p1min.tmh_hour-a.tmh_hour)
p1mina,case when p0max.tmh_hour<>0
then p0max.tmh_hour else null end p0max,p1min.tmh_hour
p1min,(c.tmh_hour-p0max.tmh_hour) cp0max,p1.tmh_group
p1g,p0.tmh_group p0g
from
(select
tmh_program_date,tmh_tpcode,tmh_equitment_code,tmh_time,tmh_state,tmh_group,tmh_cause_code,tmh_hour
from tg_machine_halt where tmh_state='A') a
left join
(select
tmh_program_date,tmh_tpcode,tmh_equitment_code,tmh_time,tmh_state,tmh_group,tmh_cause_code,tmh_hour
from tg_machine_halt where tmh_state='C') c
on a.tmh_program_date = c.tmh_program_date and
a.tmh_equitment_code = c.tmh_equitment_code and a.tmh_tpcode =
c.tmh_tpcode
left join
(select
tmh_program_date,tmh_tpcode,tmh_equitment_code,tmh_time,tmh_state,tmh_group,tmh_cause_code,tmh_hour
from tg_machine_halt where tmh_state='1') p1
on a.tmh_program_date = p1.tmh_program_date and
a.tmh_equitment_code = p1.tmh_equitment_code and a.tmh_tpcode =
p1.tmh_tpcode
left join
(select
tmh_program_date,tmh_tpcode,tmh_equitment_code,tmh_time,tmh_state,tmh_group,tmh_cause_code,tmh_hour
from tg_machine_halt where tmh_state='0') p0
on a.tmh_program_date = p0.tmh_program_date and
a.tmh_equitment_code = p0.tmh_equitment_code and a.tmh_tpcode =
p0.tmh_tpcode and p0.tmh_group =
p1.tmh_group
left join
(select
tmh_program_date,tmh_tpcode,tmh_equitment_code,tmh_time,tmh_state,tmh_group,tmh_cause_code,tmh_hour
from tg_machine_halt where tmh_state='1' and tmh_group ='1')
p1min
on a.tmh_program_date = p1min.tmh_program_date and
a.tmh_equitment_code = p1min.tmh_equitment_code and a.tmh_tpcode =
p1min.tmh_tpcode
left join
(select
tmh_program_date,tmh_tpcode,tmh_equitment_code,tmh_time,max(tmh_group)
tmh_group,tmh_cause_code,tmh_hour from tg_machine_halt where
tmh_state='0' group By
tmh_program_date,tmh_tpcode,tmh_equitment_code) p0max
on a.tmh_program_date = p0max.tmh_program_date and
a.tmh_equitment_code = p0max.tmh_equitment_code and a.tmh_tpcode =
p0max.tmh_tpcode ;