05.03.2009 17:49
deucel
 
Еще один отчетик, назовем его Реализация за месяц в закупочных ценах по партиям с учетом остатка:

специально не убирал промежуточные и контрольные данные, чтоб проще было понять логику

Код:
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
q1 - кол-во которое берем из партии
prav - правило q1
quantity - кол-во в приходе
goods - кол-во товара для раскидки по партиям (goods_in + goods_out, постоянное значение)
goods2 - нарастающий итог по партиям (sum(q1))
goods_in - остаток (постоянное значение)
goods_out - проданное кол-во (постоянное значение)
t2 - сумма q1 (проверочное значение, использовалось для отладки - sum(q1) = goods_out т.е., что у нас есть все кол-во проданного товара)

для получения конечного результата нас будет интересовать sum(q1*itemprice)