Arithmetic operations on a table can anyone give me some advice on the following situation:

I have two basic tables:




Then a view created by the two:

select product.IDPRODUCT, sum(quantity) as totalOut, PRODUCTSTORAGE.DATAINPUT, PRODUCTSTORAGE.TOTALIN
    from PRODUCT 
    inner join COMMANDPRODUCT on commandproduct.idproduct = product.idproduct 
    inner join PRODUCTSTORAGE on PRODUCT.IDPRODUCT = PRODUCTSTORAGE.IDPRODUCT
    group by product.idproduct, PRODUCTSTORAGE.DATAINPUT, PRODUCTSTORAGE.TOTALIN order by datainput asc;




How can you obsserva i have 4 records with idProduct = 28, and totalOut for all together is 18, and not individually.
To me that the result should look like the following:

I would like the result to look like this:


How could I do that?
Well, perhaps not the best way, however, I managed to do it via this: (I ignored that extra third table, wasn't sure ... not sure how it affects things) ;)
(the w_tab1 and w_tab2 are just your sample data ... )
Tested on Oracle 12.

  with w_tab1 as (
           select 28 idproduct, 18 totalout from dual union all
           select 26 idproduct,  8 totalout from dual union all 
           select 27 idproduct,  2 totalout from dual union all
           select 16 idproduct, 17 totalout from dual 
        ),
     w_tab2 as (
           select 16 idstorage, 16 idproduct, 20 totalin, 0    purchaseprice, null                                 datainput, 2 idprovider, null status from dual union all
           select 17 idstorage, 17 idproduct, 20 totalin, 0    purchaseprice, null                                 datainput, 2 idprovider, null status from dual union all
           select 22 idstorage, 22 idproduct, 20 totalin, 4500 purchaseprice, null                                 datainput, 2 idprovider, null status from dual union all
           select 23 idstorage, 23 idproduct,  6 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 2 idprovider, null status from dual union all
           select 24 idstorage, 24 idproduct,  4 totalin, 3700 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 2 idprovider, null status from dual union all
           select 25 idstorage, 25 idproduct, 20 totalin, 40   purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 2 idprovider, null status from dual union all
           select 26 idstorage, 26 idproduct, 20 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 27 idstorage, 27 idproduct, 10 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 28 idstorage, 28 idproduct, 10 totalin, 4300 purchaseprice, to_date('12-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 29 idstorage, 28 idproduct, 10 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 30 idstorage, 29 idproduct, 10 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 31 idstorage, 28 idproduct, 40 totalin, 3000 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 32 idstorage, 28 idproduct, 30 totalin, 3000 purchaseprice, to_date('22-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 33 idstorage, 30 idproduct, 10 totalin, 4300 purchaseprice, to_date('22-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual 
        ),
     w_sub as (
           select a.idproduct,
                  a.totalout, sum(b.totalin) over (partition by a.idproduct
                                   order by b.datainput, b.idstorage) sumin,
                  b.datainput, b.totalin, b.idstorage
             from w_tab1  a,
                  w_tab2  b
            where a.idproduct = b.idproduct
        )
  select idproduct, 
           least(totalin, nvl(lag(greatest(0,totalout-sumin)) over (partition by idproduct
                                order by datainput, idstorage),greatest(0,least(totalout,sumin))))       totalout, 
           datainput, totalin
    from w_sub
   order by idproduct, datainput, idstorage
  /


Result (on Oracle 12):

  IDPRODUCT   TOTALOUT DATAINPUT               TOTALIN
  ---------- ---------- -------------------- ----------
          16         17                              20
          26          8 18-may-2015 00:00:00         20
          27          2 18-may-2015 00:00:00         10
          28         10 12-may-2015 00:00:00         10
          28          8 18-may-2015 00:00:00         10
          28          0 18-may-2015 00:00:00         40
          28          0 22-may-2015 00:00:00         30

  7 rows selected.