I have this SQL that pulls exactly what I want, but I am missing the last piece. I need the SQL only to display information when the HRS is greater than 48hrs. I tried doing an if else statement but I am coming up with problems. I listed my original work that displays everything I need just missing the part where it will only display if the HRS > 48HRS. Can someone please help me with this issue? Thank you in advance!
x
Select
g.entity as MISTI,
t.cur_state as STATE,
ROUND(((SYSDATE-t.cur_state_dttm)*24),2) AS HRS
From Entity_Grp_Lst g
left outer join trk_id_def t
on g.facility = t.facility
and g.grp_type = t.grp_type
and g.entity=t.entity
and g.grp_type = '720'
WHERE t.cur_state NOT IN ('PROD','NM','TERM','NULL','IDLE', 'YER', 'PENG')
and Entity_Grp_type = '085'
and g.ENTITY_GRP = 'MET'
order by g.entity, t.cur_state_DTTM desc
Advertisement
Answer
You should add filter:
Select
g.entity as MISTI,
t.cur_state as STATE,
ROUND(((SYSDATE-t.cur_state_dttm)*24),2) AS HRS
From Entity_Grp_Lst g
left outer join trk_id_def t
on g.facility = t.facility
and g.grp_type = t.grp_type
and g.entity=t.entity
and g.grp_type = '720'
WHERE t.cur_state NOT IN ('PROD','NM','TERM','NULL','IDLE', 'YER', 'PENG')
and Entity_Grp_type = '085'
and g.ENTITY_GRP = 'MET'
AND ROUND(((SYSDATE-t.cur_state_dttm)*24),2) > 48 -- here
order by g.entity, t.cur_state_DTTM desc