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!
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