We have a view called: vu_pohyby.
It contains all incoming/outgoing goods. It has a lot of records.
It looks like this, for example:
RID |kod_o |datum_p |kod_id |partner |pocet |Cont.n.|storno|cena_dokl
0032000015044 |01 |02.10.2020 |566 |248 |360 |545 |0 |0,250
0292000046277 | |03.10.2020 |566 |326 |-6 |539 |0 |0,260
0292000046310 | |04.10.2020 |566 |326 |-6 |533 |0 |0,260
0292000046338 | |05.10.2020 |566 |325 |-6 |515 |0 |0,260
0292000046350 | |05.10.2020 |566 |326 |-6 |509 |0 |0,260
-20 | |05.10.2020 |566 | |350 |509 |0 |0,000
Continuous number ->
(sum (decode(STORNO, 0, decode(L.RID, '-20', 0,l.pocet),0)) over (ORDER BY l.datum_p ROWS UNBOUNDED PRECEDING))
I want to get the difference between prices -> income/outgoing in another table.
So i want to select 2 prices, which are linked to the highest date (datum_p).
I tried, but it's not good:
(select price_out from (
select l.kod_id, --goods
l.cena_dokl as price_out
from vu_pohyby l --table
where l.storno=0 and l.kod_o is null --canceled=0, l.kod_o->flag=out/incoming
and substr(l.rid,1,3)='029' -- 029->out
group by l.rid, l.datum_p, l.kod_id, l.cena_dokl
order by l.datum_p desc )
aa where aa.kod_id=sklad_karta.id and rownum=1)
-
(select price_inc from (
select l.kod_id,
l.cena_dokl as price_inc
from vu_pohyby l
where l.storno=0
and substr(l.rid,1,3)='003' --003->incoming
group by l.rid, l.datum_p, l.kod_id, l.cena_dokl
order by l.datum_p desc )
aa where aa.kod_id=sklad_karta.id and rownum=1)
Simplification:
I want to distract 2 prices:
1:...where MAX(datum_P), kod_o=NULL, storno=0 -> In this case 0292000046350 -> 0,26
2:...where MAX(datum_P), kod_o=01, storno=0 -> In this case 0032000015044 -> 0,25 Difference: 0,01
How can i solve this in one query ?
I think you want to use MAX( cena_dokl ) KEEP ( DENSE_RANK LAST ORDER BY datum_p ) to get the value for the latest date and then you want to filter for the price in/price out rows. Something like:
SELECT kod_id,
MAX( CASE WHEN kod_o IS NULL THEN datum_p END )
AS date_most_recent_price_out,
MAX( CASE WHEN kod_o IS NULL THEN cena_dokl END ) KEEP (
DENSE_RANK LAST
ORDER BY CASE WHEN kod_o IS NULL THEN datum_p END ASC NULLS FIRST
) AS most_recent_price_out,
MAX( CASE WHEN kod_o IS NOT NULL THEN datum_p END )
AS date_most_recent_price_in,
MAX( CASE WHEN kod_o IS NOT NULL THEN cena_dokl END ) KEEP (
DENSE_RANK LAST
ORDER BY CASE WHEN kod_o IS NOT NULL THEN datum_p END ASC NULLS FIRST
) AS most_recent_price_in
FROM vu_pohyby
WHERE storno=0
AND ( ( kod_o IS NULL AND rid LIKE '029%' )
OR ( kod_o IS NOT NULL AND rid LIKE '003%' ) )
GROUP BY
kod_id
Which, for your test data:
CREATE TABLE vu_pohyby ( RID, kod_o, datum_p, kod_id, partner, pocet, "Cont.n.", storno, cena_dokl ) AS
SELECT '0032000015044', '01', DATE '2010-10-02', 566, 248, 360, 545, 0, 0.250 FROM DUAL UNION ALL
SELECT '0292000046277', NULL, DATE '2010-10-03', 566, 326, -6, 539, 0, 0.260 FROM DUAL UNION ALL
SELECT '0292000046310', NULL, DATE '2010-10-04', 566, 326, -6, 533, 0, 0.260 FROM DUAL UNION ALL
SELECT '0292000046338', NULL, DATE '2010-10-05', 566, 325, -6, 515, 0, 0.260 FROM DUAL UNION ALL
SELECT '0292000046350', NULL, DATE '2010-10-05', 566, 326, -6, 509, 0, 0.260 FROM DUAL UNION ALL
SELECT ' -20', NULL, DATE '2010-10-05', 566, NULL, 350, 509, 0, 0.000 FROM DUAL
Outputs:
KOD_ID | DATE_MOST_RECENT_PRICE_OUT | MOST_RECENT_PRICE_OUT | DATE_MOST_RECENT_PRICE_IN | MOST_RECENT_PRICE_IN -----: | :------------------------- | --------------------: | :------------------------ | -------------------: 566 | 2010-10-05 | .26 | 2010-10-02 | .25
db<>fiddle here