select article, locid, sum(fi), avg(ost)
from (select dt,
article,
locid,
sum(quant) over(partition by locid, article order by dt) ost,
case
when (sum(quant)
over(partition by locid, article order by dt)) > 0 then
1
else
0
end as fi
from (select dt, article, sum(quantity) quant, locid
from (select d.createdat dt,
t.article,
t.quantity,
d.locationto locid
from smdocuments d, smspec t
where d.doctype in ('WI', 'IW', 'CR')
and d.docstate >= 2
and d.doctype = t.doctype
and d.id = t.docid
and d.createdat <= &DateEnd
and t.article in
(select article
from smcardassort
where idassort = &assort)
union all
select d1.createdat dt,
t1.article,
-t1.quantity quantity,
d1.locationfrom locid
from smdocuments d1, smspec t1
where d1.doctype in ('WO', 'IW', 'CS')
and d1.docstate >= 2
and d1.doctype = t1.doctype
and d1.id = t1.docid
and d1.createdat <= &DateEnd
and t1.article in
(select article
from smcardassort
where idassort = &assort)
union all
select a.dt dt, b.article, 0 quantity, c.id locid
from dates a, smcard b, smstorelocations c
where a.dt between '01.04.2004' and &dateend
and b.article in
(select article
from smcardassort
where idassort = &assort)
and c.id <> -1)
group by dt, article, locid))
where dt between &DateStart and &DateEnd
group by locid, article SELECT DISTINCT saledate, loc, article,
SUM (ost) OVER (PARTITION BY article, loc ORDER BY saledate) ost,
SUM (sum_ost) OVER (PARTITION BY article, loc ORDER BY saledate) sum_ost
FROM (SELECT saledate, loc, article, SUM (q) ost, SUM (summ2) sum_ost
FROM (SELECT d.saledate, d.article,
DECODE (d.salelocationto, -2, d.salelocationfrom, d.salelocationto) loc,
DECODE (d.salelocationto, -2, -d.saleq, d.saleq) q,
DECODE (d.salelocationto, -2, -d.primecost, d.primecost) summ2
FROM fvmaprep d
WHERE d.saledate < TO_DATE ('01.07.2013', 'dd.mm.yyyy') AND d.rectype = NVL(1,UID)
UNION ALL
SELECT /*+ PARALLEL(D, 2) */ DISTINCT a.COLUMN_VALUE saledate, d.article,
DECODE (d.salelocationto, -2, d.salelocationfrom, d.salelocationto) loc,
0 q, 0 summ2
FROM table (vit_dat_rang.gen_range (TO_DATE ('01.06.2013', 'dd.mm.yyyy'), TO_DATE ('31.06.2013', 'dd.mm.yyyy'))) a, fvmaprep d
WHERE d.saledate < TO_DATE ('01.07.2013', 'dd.mm.yyyy') AND d.rectype = NVL(1,UID)
)
GROUP BY saledate, article, loc ) CREATE OR REPLACE
package body vit_dat_rang as
function gen_range(start_date date, end_date date) return date_range_tbl
pipelined
is
cur_date date;
begin
cur_date := trunc(start_date);
while (cur_date <= trunc(end_date))
loop
pipe row(cur_date);
cur_date := cur_date + 1;
end loop;
return;
end;
end;
/ FROM table (vit_dat_rang.gen_range (TO_DATE ('01.06.2013', 'dd.mm.yyyy'), TO_DATE ('31.06.2013', 'dd.mm.yyyy'))) a, fvmaprep d
WHERE d.saledate < TO_DATE ('01.07.2013', 'dd.mm.yyyy') AND d.rectype = NVL(1,UID) Plan 23 SELECT STATEMENT ALL_ROWS Cost: 2130513900 Bytes: 2001718258 Cardinality: 141222038 Partition #: 0 22 WINDOW BUFFER Cost: 2130513900 Bytes: 2001718258 Cardinality: 141222038 Partition #: 0 21 SORT GROUP BY Cost: 2130513900 Bytes: 2001718258 Cardinality: 141222038 Partition #: 0 20 VIEW SUPERMAG. Cost: 1947031979 Bytes: 2001718258 Cardinality: 141222038 Partition #: 0 19 UNION-ALL Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0 6 VIEW SUPERMAG. Cost: 238869 Bytes: 1016996606 Cardinality: 52593702 Partition #: 0 5 UNION-ALL Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0 3 PARTITION RANGE ITERATOR Cost: 62763 Bytes: 293698528 Cardinality: 8638192 Partition #: 7 Partitions accessed #1 - #43 2 PARTITION LIST ALL Cost: 62763 Bytes: 293698528 Cardinality: 8638192 Partition #: 8 Partitions accessed #1 - #LAST 1 TABLE ACCESS FULL SUPERMAG.FFMAPREP [Analyzed] Cost: 62763 Bytes: 293698528 Cardinality: 8638192 Partition #: 8 Partitions accessed #1 - #338 4 TABLE ACCESS FULL SUPERMAG.FFMAPREP_ [Analyzed] Cost: 176106 Bytes: 1494487340 Cardinality: 43955510 Partition #: 0 18 HASH UNIQUE Cost: 1946793110 Bytes: 579591184 Cardinality: 88628336 Partition #: 0 17 MERGE JOIN CARTESIAN Cost: 83177829 Bytes: 579591184 Cardinality: 88628336 Partition #: 0 14 PX COORDINATOR Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0 13 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10000 Cost: 209307 Cardinality: 52593702 Partition #: 0 12 VIEW PARALLEL_COMBINED_WITH_PARENT SUPERMAG. Cost: 209307 Cardinality: 52593702 Partition #: 0 11 UNION-ALL PARALLEL_COMBINED_WITH_PARENT Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0 8 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost: 55543 Bytes: 215954800 Cardinality: 8638192 Partition #: 17 Partitions accessed #1 - #LAST 7 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT SUPERMAG.FFMAPREP [Analyzed] Cost: 55543 Bytes: 215954800 Cardinality: 8638192 Partition #: 17 Partitions accessed #1 - #338 10 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost: 153763 Bytes: 1098887750 Cardinality: 43955510 Partition #: 0 9 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT SUPERMAG.FFMAPREP_ [Analyzed] Cost: 153763 Bytes: 1098887750 Cardinality: 43955510 Partition #: 0 16 BUFFER SORT Cost: 83177829 Bytes: 16336 Cardinality: 8168 Partition #: 0 15 COLLECTION ITERATOR PICKLER FETCH VIT_DAT_RANG.GEN_RANGE Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0