[ОТВЕТИТЬ]
30.01.2009 13:48
konst
 
За четыре года работы на СуперМаге - объем накопившихся ошибок иногда не позволяет увидеть реальную картину...
Например - остатки товара в закупочнх ценах - при формировании отчета по аналитической БД полученные данные - достаточно сильно отличаются от данных полученных из других источников...
Поэтому родилась идея - строить эти отчеты по оперативной БД и при этом за точку отсчета брать текущие остатки....
Наш первый отчет - остатки товара в закупочных ценах.
берем текущий остаток и привязываем его по партиям к последним приходам ...
30.01.2009 13:52
deucel
 
Одна из целей быстрое получение отчета.
У меня выполняется за 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
02.02.2009 03:06
isi
 
А ты аналитику пересчитай с полной очисткой, я при закрытии периода получил приличные расхождения...
02.02.2009 07:31
OlegON
 
Цитата:
isi А ты аналитику пересчитай с полной очисткой, я при закрытии периода получил приличные расхождения...
Так о чем и речь, что достаточно мутное товародвижение поменять на оперативку.
02.02.2009 08:21
isi
 
в середине февраля С+ обещает 27 версию с переработанным товародвижением... предлагаю пока подождать
02.02.2009 10:51
konst
 
Основная идея - сделать несколько быстрых и оперативных отчетов для менегеров:
Остатки в закупочных ценах
Остатки по поставщикам
Что-то типа товарного отчета - чтобы была возможность видеть маржу по поставщикам, по товарным группам - не тогда когда себестоимость рассчитается - а в real-time.
(p.s. себестоимость считаем 1 раз в неделю на выходных)
02.02.2009 11:13
isi
 
быстрых отчетов вряд ли получится, особенно в закупочных ценах, придется лопатить всю базу дабы получить движение по партиям... второе товародвижение написать хочешь?
Согласен что есть возможность часть отчетов переписать по оперативной базе, но все, которые оперируют с партиями не получится (быстрых имею ввиду)
02.02.2009 11:25
konst
 
Запрос выложенный в начале темы - отработал за 5 минут....
Я считаю это достаточно быстро...
Основная идея - не написать новый рассчет товародвижения, а получить оперативно определенный срез данных...
При этом можно пожертвовать точностью в пользу скорости...
если проанализировать товарные остатки... то можно увидеть, что
99% товаров будут привязаны к 1-3 последним поставкам, а оставшийся 1 % можно просто рассчитать по цене последнего прихода... при этом точность данных будет ДОСТАТОЧНО высока и для оперативной работы - этого хватит... а уже бухгалтерия в своей работе будет использовать данные основанные на рассчете себестоимости...
02.02.2009 11:33
isi
 
Цитата:
konst Запрос выложенный в начале темы - отработал за 5 минут....
Я считаю это достаточно быстро...
Основная идея - не написать новый рассчет товародвижения, а получить оперативно определенный срез данных...
При этом можно пожертвовать точностью в пользу скорости...
если проанализировать товарные остатки... то можно увидеть, что
99% товаров будут привязаны к 1-3 последним поставкам, а оставшийся 1 % можно просто рассчитать по цене последнего прихода... при этом точность данных будет ДОСТАТОЧНО высока и для оперативной работы - этого хватит... а уже бухгалтерия в своей работе будет использовать данные основанные на рассчете себестоимости...
на счет достаточно высокой точности я бы не стал утверждать, особенно учитывая темпы инфляции... переубеждать не буду, но я бы не рекомендовал считать таким образом себестоимость, при таком подходе маржа может процента на 2-3 "плавать", а это очень много

но то что у Супермага собственный расчет себестоимости тоже "хромает", может в этом есть смысл, хотя в данном случае ответственность несет разработчик, а вот если сам напишешь, то тебе и отвечать и доказывать...
04.02.2009 13:08
akonev
 
если чуть задуматься, то для оперативной работы манагеров этот подход мне кажется совершенно логичным. действительно, если товар сейчас лежит в магазине, то это может быть только товар из последних приходов.

те же остатки по поставщикам - вообще нереально посчитать по другому на базе, которую 4 года "портили". особенно, если базу обрезать.

сложнее с товарами, по которым текущий расчетный остаток не совпадает с реальным (пересорты, к примеру): излишек по пересорту полезет привязываться к старым приходам и будет портить картинку. недостача по пересорту - вообще никуда не привяжется и занизит задолженность перед поставщиком

я уж не говорю о том, что манагерский и буховский (по стандартному расчету СС) учеты должны безнадежно разползтись в разные стороны.

предлагаю подумать такую мысль: озвученный вариант манагерских отчетов - не есть ли это путь к внедрению практики периодической обрезки баз без потери данных по поставщикам.
05.02.2009 18:48
deucel
 
Цитата:
Andrew_Konev те же остатки по поставщикам - вообще нереально посчитать по другому на базе, которую 4 года "портили". особенно, если базу обрезать.

сложнее с товарами, по которым текущий расчетный остаток не совпадает с реальным (пересорты, к примеру): излишек по пересорту полезет привязываться к старым приходам и будет портить картинку. недостача по пересорту - вообще никуда не привяжется и занизит задолженность перед поставщиком
Все верно, но нужно искать компромисс.
Если предположить, что пересорт возможен только на взаимозаменяемых товарах, то сумма по группе должна быть относительно точной.

Раз была затронута тема с поставщиками, то еще один запросик попытки вычислить остатки по магазинам в закупочных ценах по поставщикам и поставкам (относительно точно и быстро). За основу был взят запрос в начале темы. Специально не группировал, чтоб было видно привязку к партиям по датам.

Код:
SELECT   z.LOCATION, z.article, z.createdat, NVL (z.clientindex, -1) clientindex, z.goods, 
         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
               THEN z.quantity
            WHEN z.goods2 - z.quantity < z.goods AND z.goods < z.goods2
               THEN z.quantity - (z.goods2 - z.goods)
         END quantity,
         (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
         ) totalprice
    FROM (SELECT g.LOCATION, g.article, s.clientindex, s.createdat, g.goods,
                 CASE
                    WHEN COUNT (s.quantity) OVER (PARTITION BY g.LOCATION, g.article) = 1
                       THEN g.goods
                    ELSE s.quantity
                 END 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,
                 CASE
                    WHEN COUNT (s.quantity) OVER (PARTITION BY g.LOCATION, g.article) = 1
                       THEN g.goods
                    ELSE SUM (s.quantity) OVER (PARTITION BY g.LOCATION, g.article ORDER BY s.createdat DESC)
                 END goods2
            FROM (SELECT storeloc LOCATION, article, quantity goods
                    FROM smgoods g
                   WHERE g.quantity > 0 AND EXISTS (SELECT /*+ INDEX (C)*/
                                                           1
                                                      FROM smcard c
                                                     WHERE c.datatype = 0 AND c.article = g.article)) g,
                 (SELECT   /*+ INDEX(D)*/
                           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 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) s
           WHERE g.LOCATION = s.LOCATION(+) AND g.article = s.article(+)
          UNION ALL
          SELECT g.LOCATION, g.article, NULL clientindex, NULL createdat, g.goods, NULL quantity,
                 NVL ((SELECT p.price
                         FROM smprices p
                        WHERE p.storeloc = g.LOCATION AND p.article = g.article AND p.pricetype = 6), 0) totalprice, NULL goods2
            FROM (SELECT storeloc LOCATION, article, quantity goods
                    FROM smgoods g
                   WHERE g.quantity < 0 AND EXISTS (SELECT /*+ INDEX (C)*/
                                                           1
                                                      FROM smcard c
                                                     WHERE c.datatype = 0 AND c.article = g.article)) g) z
   WHERE z.quantity IS NULL OR z.goods2 - z.quantity < z.goods
Чем меньше магазинов - тем быстрее отработает (сейчас по всем)
Чем больше период (сейчас 90 дней) - тем больше точность данных привязки к партиям.
Отрицательные остатки изначально к поставщикам не привязываем и используем pricetype = 6, это же относится и к положительным если не обнаружено ни одной поставки (если и это невозможно то цена 0). Если были поставки которых не достаточно для перекрытия остатка, то не перекрытый приходами остаток будет привязан к цене и соотв. поставщику самой ранней по дате.
06.02.2009 08:12
konst
 
Небольшое разъяснение - pricetype = 6 - в нашем случае это еще один вид розничной цены с 0 наценкой - привязанный ко всем местам хранения - Цена Последнего Прихода.
В первую очередь данный вид цены используется для проведения инвентаризаций ... и не только.
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)
05.03.2009 17:51
deucel
 
Единственное, что не могу пока достойно реализовать в sql - это динамический отбор документов для раскидки по партиям.
Мож кто мысль подкинет интересную.
:)
19.03.2009 11:48
Mtirt
 
Уточни пожалуйста, то ты понимаешь под динамическим отбором документов?
19.03.2009 12:08
deucel
 
Не указание определенного диапазона дат, а до момента пока не закроется нужное количество.
У меня есть несколько вариантов как можно это сделать (временные таблицы, процедуры...), но скорость работы при этом заметно падает.
В текущем варианте довольно приемлемая скорость работы при условии, что для большего процента товаров (>95%) мы находим все необходимые партии (конечно с излишней выборкой документов), остальное округляется до последней партии или цены последнего прихода (p.pricetype = 6, можно былоб и запросом находить но важна скорость).
Соотв. для принятия решений результаты вполне подходят.
Опции темы


Часовой пояс GMT +3, время: 20:37.

 

Форум сделан на основе vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd. Перевод: zCarot и OlegON
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.