Код:
--средний остаток за переиод (01.01.2013 по 05.01.2013)
--остатки считаются на утро дня
-- формула ср.ост=(ост 1 день/2 + остатки на каждый день кроме первого и последнего
-- + ост на последний день/2)/количество дней в периоде
select loc, article, sum(ost)/(TO_DATE('05.12.2013', 'dd.mm.yyyy')-TO_DATE('01.12.2013', 'dd.mm.yyyy')+1) sumost
from (
select distinct saledate, loc, article, sum(q) over(partition by article, loc order by saledate) ost
from (
-- Остаток на первый день деленный на 2 (на утро 01.01.2013)
select TO_DATE('31.12.2012', 'dd.mm.yyyy') saledate, Article, Location loc, - SUM(Quantity)/2 q
from ( SELECT /*+ ORDERED USE_NL(S) INDEX(D SMDOCUMENTS_CREATEDAT) INDEX(S) */ L.ID Location, S.Article,
SUM( S.Quantity * DECODE(L.ID,D.LocationTo,1,D.LocationFrom,-1,0)) Quantity
FROM SmDocuments D , SMStoreLocations L, SmSpec S
WHERE D.DocType = S.DocType
and D.ID = S.DocID
and L.ID in (D.LocationTo,D.LocationFrom)
and L.ID not in (-1,1)
and D.DocState >= 2
and D.CreatedAt > to_date('31.12.2012','DD.MM.YYYY')
GROUP BY L.ID, S.Article HAVING SUM( S.Quantity * DECODE(L.ID,D.LocationTo,1,D.LocationFrom,-1,0)) <> 0
UNION ALL
SELECT StoreLoc, Article, - Quantity
FROM SMGoods
WHERE Quantity <> 0 and StoreLoc not in (-1,1) )
group by Location, Article having SUM(Quantity) <> 0
UNION ALL
-- остаток на каждый день, кроме первого и последнего (на утро 2,3,4)
select d.saledate,
d.article,
decode(d.salelocationto, -2, d.salelocationfrom, d.salelocationto) loc,
sum(decode(d.salelocationto, -2, -d.saleq, d.saleq)) q
from ffmaprep d
where d.rectype=1
and d.saledate BETWEEN TO_DATE('01.01.2013', 'dd.mm.yyyy') and TO_DATE('03.01.2013', 'dd.mm.yyyy')
group by d.saledate, d.article, d.salelocationfrom, d.salelocationto
UNION ALL
select d.saledate,
d.article,
decode(d.salelocationto, -2, d.salelocationfrom, d.salelocationto) loc,
sum(decode(d.salelocationto, -2, -d.saleq, d.saleq)) q
from ffmaprep_ d
where d.rectype=1
and d.saledate BETWEEN TO_DATE('01.01.2013', 'dd.mm.yyyy') and TO_DATE('03.01.2013', 'dd.mm.yyyy')
-- and decode(d.salelocationto, -2, d.salelocationfrom, d.salelocationto)=4
-- and d.article ='02104'
group by d.saledate, d.article, d.salelocationfrom, d.salelocationto )
UNION ALL
--Остаток на последний день деленный на 2 (на утро 05.01.2013)
select TO_DATE('04.12.2013', 'dd.mm.yyyy') saledate, Location loc, Article, - SUM(Quantity)/2 ost
from ( SELECT /*+ ORDERED USE_NL(S) INDEX(D SMDOCUMENTS_CREATEDAT) INDEX(S) */ L.ID Location, S.Article,
SUM( S.Quantity * DECODE(L.ID,D.LocationTo,1,D.LocationFrom,-1,0)) Quantity
FROM SmDocuments D , SMStoreLocations L, SmSpec S
WHERE D.DocType = S.DocType
and D.ID = S.DocID
and L.ID in (D.LocationTo,D.LocationFrom)
and L.ID not in (-1,1)
and D.DocState >= 2
and D.CreatedAt > to_date('04.01.2013','DD.MM.YYYY')
GROUP BY L.ID, S.Article HAVING SUM( S.Quantity * DECODE(L.ID,D.LocationTo,1,D.LocationFrom,-1,0)) <> 0
UNION ALL
SELECT StoreLoc, Article, - Quantity
FROM SMGoods
WHERE Quantity <> 0 and StoreLoc not in (-1,1) )
group by Location, Article having SUM(Quantity) <> 0
) group by loc, article