Перестройка компании Кардекс

Question

Я пытаюсь восстановить свою компанию kardex. Мне нужно использовать "Метод средней стоимости", который должен учитывать среднюю стоимость для каждой транзакции в определенное время.

У меня есть простая таблица со всеми входящими / исходящими операциями с запасами (все ходы).

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

Проблема: все транзакции продажи (исходящие запасы) не имеют затрат на единицу продукции (потому что мне нужно перестроить эти затраты). Я знаю, что могу использовать курсоры, но мы все знаем о проблемах производительности там.

Я подготовил простые данные, чтобы лучше объяснить себя. Обратите внимание, что все продажи (исходящие транзакции) не требуют затрат, и я думаю, что использование простых оконных функций не поможет, потому что каждая строка зависит от предыдущего расчета. Итак, я вроде нужен расчет на основе предыдущего расчета..??

DECLARE @Resultado TABLE (Row_ID BIGINT IDENTITY NOT NULL PRIMARY KEY, Inventory_Name VARCHAR(100), Transaction_Date DATETIME, DIRECTION BIT, Transaction_Name VARCHAR(100),Transaction_Quantity INT, Transaction_UnitCost NUMERIC(18,2), Transaction_Amount NUMERIC(18,2), AVERAGE_UNITCOST_AT_TRANSACTION_DATE NUMERIC(18,2))

INSERT INTO @Resultado ( Inventory_Name , Transaction_Date , DIRECTION , Transaction_Name , Transaction_Quantity , Transaction_UnitCost, Transaction_Amount, AVERAGE_UNITCOST_AT_TRANSACTION_DATE)
SELECT 'COMPUTER', '2017-01-01', 1, 'INCOMING PRODUCT; PRUCHASE', 100, 10, 100 * 10, 10
UNION ALL SELECT 'COMPUTER', '2017-01-02', 1, 'INCOMING PRODUCT; PURCHASE', 105, 11, 105 * 11, 10.51
UNION ALL SELECT 'COMPUTER', '2017-01-03', 1, 'INCOMING PRODUCT; PURCHASE', 110, 12, 110 * 12, 11.03
UNION ALL SELECT 'COMPUTER', '2017-01-04', 0, 'OUTGOING PRODUCT; SALES', -200, NULL, NULL, NULL
UNION ALL SELECT 'COMPUTER', '2017-01-05', 0, 'OUTGOING PRODUCT; SALES', -50, NULL, NULL, NULL
UNION ALL SELECT 'COMPUTER', '2017-01-06', 1, 'INCOMING PRODUCT; PURCHASE', 110, 10, 110 * 10, NULL
UNION ALL SELECT 'COMPUTER', '2017-01-07', 0, 'OUTGOING PRODUCT; SALES', -20, NULL, NULL, NULL
UNION ALL SELECT 'COMPUTER', '2017-01-08', 0, 'OUTGOING PRODUCT; SALES', -20, NULL, NULL, NULL
UNION ALL SELECT 'COMPUTER', '2017-01-09', 0, 'OUTGOING PRODUCT; SALES', -20, NULL, NULL, NULL

SELECT * FROM @Resultado

Answer 1

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

Это общее табличное выражение (CTE) должно помочь вам начать:

with CTE as (
  select Row_Id, Inventory_Name, Transaction_Date, Direction, Transaction_Name,
    Transaction_Quantity, Transaction_UnitCost,
    Transaction_Quantity as QuantityOnHand,
    Transaction_UnitCost as AverageUnitCost
    from @Resultado
    where Direction = 1 and Row_Id = 1 -- Starting condition for your single product sample data.
  union all
  select R.Row_ID, R.Inventory_Name, R.Transaction_Date, R.Direction, R.Transaction_Name,
    R.Transaction_Quantity, R.Transaction_UnitCost,
    -- The   TransactionQuantity   is already signed, so there is no need to multiple it by   ( Direction * 2 - 1 ) .
    CTE.QuantityOnHand + R.Transaction_Quantity,
    -- My accounting is pretty rusty, but this should do some sort of useful averaging.
    Cast( case
      when R.Direction = 0 then CTE.AverageUnitCost -- Sales don't affect the average unit cost.
      else ( CTE.AverageUnitCost * CTE.QuantityOnHand + R.Transaction_UnitCost * R.Transaction_Quantity ) /
        ( CTE.QuantityOnHand + R.Transaction_Quantity ) end
      as Numeric(18,2) )
    from CTE inner join
      @Resultado as R on R.Row_Id = CTE.Row_Id + 1 -- Row by row.
    )
  select Row_Id, Inventory_Name, Transaction_Date, Direction, Transaction_Name, Transaction_Quantity, Transaction_UnitCost,
    QuantityOnHand, AverageUnitCost
    from CTE;

Вы можете запустить его для своих образцов данных после добавления точки с запятой в конце select заявление.

Производительность, скорее всего, будет низкой, так как вам нужно начинать вычисления с первой строки. Добавление столбцов для поддержания QuantityOnHand а также AveragreUnitCost позволит вам обновлять данные по мере появления новых транзакций, например, в триггере. (Обратите внимание, что обновления и удаления могут потребовать пересчета с начала времени.)

Более современное решение будет использовать Lead / Lag, но я случайно вернулся на SQL Server 2008 R2.