oracle - How do a repeat a query for past 30 days without union 30 times? -
i have query returns value today (sysdate) best way repeat query past 30 days without creating 30 separate queries unioned together?
for example here basic query:
select sum(duration)/3600 ign_oee_events_d asset_id = 1978 , event_type = 1 , shift = 2 , trunc(event_dt)=trunc(sysdate)
what avoid repeating query this:
select sum(duration)/3600 ign_oee_events_d asset_id = 1978 , event_type = 1 , shift = 2 , trunc(event_dt)=trunc(sysdate) union select sum(duration)/3600 ign_oee_events_d asset_id = 1978 , event_type = 1 , shift = 2 , trunc(event_dt)=trunc(sysdate)-1 union select sum(duration)/3600 ign_oee_events_d asset_id = 1978 , event_type = 1 , shift = 2 , trunc(event_dt)=trunc(sysdate)-2 union select sum(duration)/3600 ign_oee_events_d asset_id = 1978 , event_type = 1 , shift = 2 , trunc(event_dt)=trunc(sysdate)-3
is there way this?
repeat dates event_dt between sysdate , sysdate -30 select sum(duration)/3600 ign_oee_events_d asset_id = 1978 , event_type = 1 , shift = 2
thank you.
it sounds need group by
. i'm guessing want order by
select trunc(event_dt), sum(duration)/3600 ign_oee_events_d asset_id = 1978 , event_type = 1 , shift = 2 , trunc(event_dt) >= trunc(sysdate)-30 group trunc(event_dt) order trunc(event_dt)
Comments
Post a Comment