Еще один отчетик, назовем его Реализация за месяц в закупочных ценах по партиям с учетом остатка:
специально не убирал промежуточные и контрольные данные, чтоб проще было понять логику
Код:
SELECT *
FROM (SELECT s2.*, SUM (NVL (s2.q1, 0)) OVER (PARTITION BY s2.LOCATION, s2.article) t2
FROM (SELECT s.LOCATION, s.article, s.clientindex, s.createdat,
CASE
-- Если только одна запись для артикула (с приходами или без).
WHEN COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article) = 1
THEN s.goods - s.goods_in
-- Если нарастающий итог приходов больше.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods
THEN NULL
-- Если нарастающий итог приходов меньше или равен остатку, но есть еще приходы.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <= s.goods_in
AND ROW_NUMBER () OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <
COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article)
THEN NULL
-- Если сумма нарастающего итога приходов и равна остатку.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) = s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity
ELSE s.goods - s.goods_in
END
-- Если последняя запись нарастающего итога приходов меньше общего остатка.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity + (s.goods - SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex))
ELSE s.goods - s.goods_in
END
-- Если последняя запись нарастающего итога приходов и больше остатка.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity - (SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.goods)
ELSE s.goods - s.goods_in
END
-- Если сумма нарастающего итога меньше остатка
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity
ELSE SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.goods_in
END
-- Если сумма нарастающего итога больше остатка
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity - (SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.goods)
ELSE s.goods - s.goods_in
END
-- ELSE s.quantity
-- >>>
END q1,
-- Какое правило отработало
CASE
-- Если только одна запись для артикула (с приходами или без).
WHEN COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article) = 1
THEN '<< 1'
-- Если нарастающий итог приходов больше.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods
THEN '<< 2'
-- Если нарастающий итог приходов меньше или равен остатку, но есть еще приходы.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <= s.goods_in
AND ROW_NUMBER () OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <
COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article)
THEN '<< 3'
-- Если сумма нарастающего итога приходов и равна остатку.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) = s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN '<< 4a'
ELSE '<< 4b'
END
-- Если последняя запись нарастающего итога приходов меньше общего остатка.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN '<< 5a'
ELSE '<< 5b'
END
-- Если последняя запись нарастающего итога приходов и больше остатка.
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN '<< 6a'
ELSE '<< 6b'
END
-- Если сумма нарастающего итога меньше остатка
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN '<< 7a'
ELSE '<< 7b'
END
-- Если сумма нарастающего итога больше остатка
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN '<< 8a'
ELSE '<< 8b'
END
ELSE '<< 9'
END prav,
CASE
WHEN COUNT (s.quantity) OVER (PARTITION BY s.LOCATION, s.article) = 1
THEN s.goods
ELSE s.quantity
END quantity,
NVL (s.totalprice / DECODE (s.quantity, 0, NULL),
NVL ((SELECT p.price
FROM smprices p
WHERE p.storeloc = s.LOCATION AND p.article = s.article AND p.pricetype = 6), 0)) itemprice, s.goods,
CASE
WHEN COUNT (s.quantity) OVER (PARTITION BY s.LOCATION, s.article) = 1
THEN s.goods
ELSE SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex)
END goods2,
s.goods_in, s.goods_out
FROM (SELECT s.LOCATION, s.article, d.clientindex, d.createdat, NVL (d.quantity, 0) quantity, s.goods_out + NVL (g.quantity, 0) goods,
NVL (g.quantity, 0) goods_in, s.goods_out, NVL (d.totalprice, 0) totalprice
FROM (SELECT /*+ INDEX(D) INDEX(S)*/
d.locationfrom LOCATION, s.article, SUM (s.quantity) goods_out
FROM smspec s, smdocuments d
WHERE s.doctype = d.doctype AND s.docid = d.ID AND d.doctype = 'CS' AND d.createdat BETWEEN TRUNC (SYSDATE, 'MON') AND SYSDATE
GROUP BY d.locationfrom, s.article) s,
(SELECT storeloc, article, quantity
FROM smgoods
WHERE quantity > 0) g,
(SELECT /*+ INDEX(D) INDEX(S)*/
d.locationto LOCATION, d.clientindex, 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 - (90 + (TRUNC (SYSDATE) - TRUNC (SYSDATE, 'MON')))
AND d.docstate = 3
AND d.opcode != 9
AND s.doctype = d.doctype
AND s.docid = d.ID
GROUP BY d.locationto, d.clientindex, d.createdat, s.article) d
WHERE s.LOCATION = g.storeloc(+) AND s.article = g.article(+) AND s.LOCATION = d.LOCATION(+) AND s.article = d.article(+)) s) s2
WHERE s2.q1 IS NOT NULL)
-- WHERE t2 != goods_out
goods - кол-во товара для раскидки по партиям (goods_in + goods_out, постоянное значение)
t2 - сумма q1 (проверочное значение, использовалось для отладки - sum(q1) = goods_out т.е., что у нас есть все кол-во проданного товара)