ИнфоАптека ®

Автоматизация аптек и аптечных сетей

Инструменты пользователя

Инструменты сайта


user:vmsirenko:устойчивая_дефектура_или_ошибки_норамтивов_запасов

Устойчивая дефектура или ошибки норамтивов запасов

В процессе работы над автоматическим сравнением заявок с поставками, написал запрос показывающий товары, попадающие в Неразмезщенную заявку за последние 3 дня, ежедневно.

Причины:

  • Отказ поставщика в заявке.
  • Городская дефектура.
  • Ошибка в нормативах запасов.

SQL

Вариант 1

orders_repeat1.sql
SELECT
  oi.med_id AS id,
  med.med_name,
  ven.vendor_name,
  dp.dep_name,
  avg(oi.qtty)
FROM orders ord, orderitem oi
INNER JOIN medicine med ON med.med_id=oi.med_id
INNER JOIN department dp ON dp.dep_id=ord.dep_id
INNER JOIN vendor ven ON ven.vendor_id=med.vendor_id
WHERE
  oi.order_id=ord.order_id
  AND ord.orderdate>=dateadd(DAY, -4, CURRENT_DATE)
  AND ord.ordertype IN (1,2)
GROUP BY 1,2,3,4
HAVING COUNT(CAST(ord.orderdate AS DATE))>=3
ORDER BY 2,4

Вариант 2.1

В отличие от первого варианта, этот запрос наглядно отображает повторяющиеся товары в заявках, без группировки по товару. Но и работает несколько дольше из-за вложенного запроса, на моей базе ~40 минут.

order_repeat2.1.sql
SELECT
  ord.order_id,
  ord.orderdate,
  oi.med_id,
  med.med_name,
  ven.vendor_name,
  dp.dep_name,
  oi.qtty
FROM orders ord, orderitem oi
INNER JOIN medicine med ON med.med_id=oi.med_id
INNER JOIN department dp ON dp.dep_id=ord.dep_id
INNER JOIN vendor ven ON ven.vendor_id=med.vendor_id
WHERE oi.order_id=ord.order_id
  AND ord.orderdate>=dateadd(DAY, -4, CURRENT_DATE)
  AND ord.ordertype IN (1,2)
  AND EXISTS (
SELECT
  oi1.med_id,
  ord1.dep_id,
  ord1.orderdate
FROM orders ord1, orderitem oi1
WHERE oi1.order_id=ord1.order_id
  AND ord1.orderdate>=dateadd(DAY, -4, CURRENT_DATE)
  AND ord1.ordertype IN (1,2)
  AND oi1.med_id=oi.med_id AND ord1.dep_id=ord.dep_id
GROUP BY 1,2,3
HAVING COUNT(CAST(ord1.orderdate AS DATE))>=3
ORDER BY 1,2,3)
ORDER BY 4,6

Вариант 2.2

Этот вариант уже не просто запрос, а скрипт и он аналогичен запросу по Варианту 2.1, но работает гораздо быстрее.

order_repeat2.2.sql
SET SQL DIALECT 3;
 
SET NAMES WIN1251;
 
CONNECT 'aptsrv/3052:C:\iaDb\IAPTEKA.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
 
SET AUTODDL ON;
 
SET list ON;
 
SET term ^;
 
EXECUTE block RETURNS (o_id INTEGER, o_date DATE, o_med_id INTEGER, o_med_name VARCHAR(200), o_vendor_name VARCHAR(150), o_dep_name VARCHAR(40), o_qtty INTEGER) AS
DECLARE variable ord_med INTEGER;
DECLARE variable ord_dep INTEGER;
BEGIN
  FOR SELECT
    oi.med_id,
    ord.dep_id
  FROM orders ord, orderitem oi
  WHERE oi.order_id=ord.order_id
    AND ord.orderdate>=dateadd(DAY, -4, CURRENT_DATE)
    AND ord.ordertype IN (1,2)
  GROUP BY 1,2
  HAVING COUNT(CAST(ord.orderdate AS DATE))>2
  ORDER BY 1,2
  INTO :ord_med, :ord_dep do
    FOR SELECT
      ord.order_id,
      ord.orderdate,
      oi.med_id,
      med.med_name,
      ven.vendor_name,
      dp.dep_name,
      oi.qtty
    FROM orders ord, orderitem oi
    INNER JOIN medicine med ON med.med_id=oi.med_id
    INNER JOIN department dp ON dp.dep_id=ord.dep_id
    INNER JOIN vendor ven ON ven.vendor_id=med.vendor_id
    WHERE oi.order_id=ord.order_id
      AND ord.orderdate>=dateadd(DAY, -4, CURRENT_DATE)
      AND ord.ordertype IN (1,2)
      AND oi.med_id=:ord_med AND ord.dep_id=:ord_dep
    INTO :o_id, :o_date, :o_med_id, :o_med_name, :o_vendor_name, :o_dep_name, :o_qtty do
    suspend;
END^
 
SET term ;^
 
commit;
 
QUIT;
Только авторизованные участники могут оставлять комментарии.
user/vmsirenko/устойчивая_дефектура_или_ошибки_норамтивов_запасов.txt · Последние изменения: 2017/02/22 16:48 — Владимир Сиренко