Skip to content
Advertisement

SQL If else statement dealing with current time

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
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement