Home » SQL & PL/SQL » SQL & PL/SQL » Improve nested Query, if possible (10.2.0)
Improve nested Query, if possible [message #655745] |
Sun, 11 September 2016 03:05 |
|
bluetooth420
Messages: 146 Registered: November 2011
|
Senior Member |
|
|
Hi,
I have mad a query according to requirement. Can someone improve it further with respect to design and efficiency?
create table demand
(invdate date,
pcode number(2),
qty number(4)
);
create table supply
(invdate date,
pcode number(2),
qty number(4)
);
--Sample data
insert into demand values ('05-AUG-2016', 1, 5);
insert into demand values ('05-SEP-2016', 1, 10);
insert into demand values ('06-SEP-2016', 1, 8);
insert into demand values ('08-SEP-2016', 2, 9);
insert into demand values ('09-SEP-2016', 3, 4);
insert into supply values ('05-SEP-2016', 1, 5);
insert into supply values ('06-SEP-2016', 1, 4);
insert into supply values ('07-SEP-2016', 1, 2);
insert into supply values ('08-SEP-2016', 1, 1);
insert into supply values ('10-SEP-2016', 3, 10);
Select invdate, pcode, tot_demand, tot_supply
from (
select invdate,
pcode,
sum(qty) tot_demand,
( select nvl(sum(qty),0)
from supply b
where b.pcode=a.pcode
and b.invdate>a.invdate) as tot_supply
from demand a
where trunc(sysdate)-invdate<=10
and (pcode, invdate) in (select pcode, max(invdate)
from demand
group by pcode)
group by invdate, pcode)
where tot_demand>tot_supply
INVDATE PCODE TOT_DEMAND TOT_SUPPLY
--------- ---------- ---------- ----------
06-SEP-16 1 8 3
08-SEP-16 2 9 0
Rules:
1) ignore 10 days old demand
2) ignore current date and previous date of supply date while comparing demand date with supply date.
3) ignore if supply has exceeded demand date
4) pick latest demand of a product if more than 1 falls in last 10 days
Required: Find remaining demand of each product left over under above rules
Thanks
|
|
|
Re: Improve nested Query, if possible [message #655746 is a reply to message #655745] |
Sun, 11 September 2016 08:49 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Rule 4 states: ignore if supply has exceeded demand date while your code selects supply dates exceeding demand dates - b.invdate>a.invdate. Anyway, something like:
with t as (
select max(invdate) invdate, --pick latest demand of a product if more than 1 falls in last 10 days
pcode,
sum(qty) keep(dense_rank last order by invdate) tot_demand -- pick latest demand of a product if more than 1 falls in last 10 days
from demand
where trunc(sysdate) - invdate <= 10 -- ignore 10 days old demand
group by pcode
)
select invdate,
pcode,
tot_demand,
nvl(
(
select sum(b.qty)
from supply b
where b.pcode = t.pcode
and b.invdate <= t.invdate -- ignore if supply has exceeded demand date
and b.invdate < trunc(sysdate) - 1 -- ignore current date and previous date of supply date while comparing demand date with supply date
),
0
) as tot_supply
from t
/
INVDATE PCODE TOT_DEMAND TOT_SUPPLY
--------- ---------- ---------- ----------
06-SEP-16 1 8 9
08-SEP-16 2 9 0
09-SEP-16 3 4 0
SQL>
SY.
|
|
|
|
|
|
|
Re: Improve nested Query, if possible [message #655772 is a reply to message #655750] |
Mon, 12 September 2016 06:59 |
|
Flyby
Messages: 188 Registered: March 2011 Location: Belgium
|
Senior Member |
|
|
in the t-with, you could replace where trunc(sysdate) - invdate <= 10 with where invdate >= trunc(sysdate) - 10 (at most 10 days old)
In that way the engine doens't has to calculate the difference for each row and might use an index to compare with trunc(sysdate) - 10
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:57:28 CDT 2024
|