Hello everyone,
Need help from SQL gurus
Please look at following query:
what I want to get here is obvious I think: for each combination of prh_site and prh_part I need sum of prh_rcvd
for maximum value of prh_rcp_date. When I try to run this I get error from the subject (Progress 9.1E).
I tried also this:
This one works, but executing takes too long.
Any ideas, how can I rewrite it to get it working ?
Thanks in advance.
Need help from SQL gurus
Please look at following query:
Code:
select prh_site, prh_part, prh_rcp_date, sum(prh_rcvd)
from pub.prh_hist p1
where p1.prh_rcp_date > sysdate - 30
group by p1.prh_site, p1.prh_part, prh_rcp_date
having p1.prh_rcp_date = (select max(p2.prh_rcp_date)
from pub.prh_hist p2
where p2.prh_site = p1.prh_site and
p2.prh_part = p1.prh_part
group by p2.prh_site, p2.prh_part)
what I want to get here is obvious I think: for each combination of prh_site and prh_part I need sum of prh_rcvd
for maximum value of prh_rcp_date. When I try to run this I get error from the subject (Progress 9.1E).
I tried also this:
Code:
select p1.prh_site, p1.prh_part, p1.prh_rcp_date, sum(p1.prh_rcvd)
from pub.prh_hist p1
where p1.prh_rcp_date = (select max(p2.prh_rcp_date) from
pub.prh_hist p2
where p1.prh_site = p2.prh_site and
p1.prh_part = p2.prh_part and
p2.prh_rcp_date > sysdate - 30
group by p2.prh_site, p2.prh_part)
group by p1.prh_site, p1.prh_part, p1.prh_rcp_date
This one works, but executing takes too long.
Any ideas, how can I rewrite it to get it working ?
Thanks in advance.