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

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>