Одна из целей
быстрое получение отчета.
У меня выполняется за 5 минут и кол-во строк результата ~500тыс.
Код:
SELECT s.LOCATION, s.article, s.goods,
ROUND ((CASE
WHEN s.goods = s.quantity --когда на весь остаток есть приходы
THEN s.totalprice
WHEN s.goods != s.quantity AND s.totalprice != 0 --когда был один приход, но меньше остатка и цена не равна 0
THEN s.totalprice / s.quantity * s.goods --вычисляем цену
ELSE 0 --при всех других вариантах цена=0руб.
END
),
2
) totalprice
FROM (SELECT z.LOCATION, z.article, AVG (z.goods) goods, --получаем остатк
SUM (CASE
WHEN z.quantity IS NULL
THEN z.goods
WHEN z.goods2 - z.quantity >= z.goods
THEN null
WHEN z.goods2 - z.quantity < z.goods AND z.goods >= z.goods2 --если приходов нет, будем вычислять по ЦПП(0%)
THEN z.quantity --когда кол-во приходов меньше остатка
WHEN z.goods2 - z.quantity < z.goods AND z.goods < z.goods2
THEN z.quantity - (z.goods2 - z.goods) --корректируем кол-во, когда приходов больше чем остаток
END
) AS quantity,
SUM (CASE
WHEN z.quantity IS NULL
THEN z.goods * z.itemprice
WHEN z.goods2 - z.quantity >= z.goods
THEN null
WHEN z.goods2 - z.quantity < z.goods AND z.goods >= z.goods2
THEN z.quantity * z.itemprice
WHEN z.goods2 - z.quantity < z.goods AND z.goods < z.goods2
THEN (z.quantity - (z.goods2 - z.goods)) * z.itemprice
END
) AS totalprice
FROM (SELECT g.LOCATION, g.article, s.createdat, g.goods, s.quantity,
NVL (s.totalprice / s.quantity, NVL ((SELECT p.price
FROM smprices p
WHERE p.storeloc = g.LOCATION AND p.article = g.article AND p.pricetype = 6), 0)) itemprice,
--вычисляем цену, если нет то берем ЦПП(0%),
--если нет 0 (вариантов может быть много)
SUM (s.quantity) OVER (PARTITION BY g.LOCATION, g.article ORDER BY s.createdat DESC) goods2
/*ROW_NUMBER () OVER (PARTITION BY g.LOCATION, g.article ORDER BY s.createdat DESC) seq*/ --для проверки номера партии для артикула
FROM (SELECT storeloc LOCATION, article, quantity goods
FROM smgoods g
WHERE g.quantity != 0 /*AND storeloc = 3*/ -- для отбора по конкретному МХ
) g,
(SELECT d.locationto LOCATION, d.createdat, s.article, SUM (s.quantity) quantity, SUM (s.totalprice) totalprice
--если не сделать суммирование, то возникает проблема при расчете партии
--если в один день было несколько приходов (получаем какбы одну поставку)
FROM smdocuments d, smspec s
WHERE d.doctype = 'WI' --только приходные накладные
AND d.createdat > SYSDATE - 60 --за последние Х дней
AND d.docstate = 3 --статус "Принят полностью"
AND d.opcode != 9 --не учитывать статус "Инвентаризация излишков", т.е. приходки на основании Сличительных ведомостей
AND s.doctype = d.doctype
AND s.docid = d.ID
GROUP BY d.locationto, d.createdat, s.article) s
WHERE g.LOCATION = s.LOCATION(+) AND g.article = s.article(+) AND g.goods > 0
UNION ALL
-- отбор данных для минусовых остатков
SELECT g.LOCATION, g.article, NULL createdat, g.goods, NULL quantity,
-- NVL ((SELECT /*+ ORDERED USE_NL(S D)*/ MAX (s.itemprice)KEEP (DENSE_RANK LAST ORDER BY d.createdat) itemprice
-- FROM smdocuments d, smspec s
-- WHERE s.doctype = d.doctype AND s.docid = d.ID AND d.doctype = 'WI' AND d.locationto = g.LOCATION AND s.article = g.article), 0) totalprice
-- находит цену последнего прихода,
--но следующий вариант работает быстрее (оставлен как демонстрация возможности)
NVL ((SELECT p.price
FROM smprices p
WHERE p.storeloc = g.LOCATION AND p.article = g.article AND p.pricetype = 6), 0) totalprice
--цена (вид цены) для минусовых остатков,
--у меня вид цены ЦПП(0%) без округления
, NULL goods2
FROM (SELECT storeloc LOCATION, article, quantity goods
FROM smgoods g
WHERE g.quantity != 0 /*AND storeloc = 3*/ -- для отбора по конкретному МХ
) g
WHERE g.goods < 0) z
GROUP BY z.LOCATION, z.article) s