Вставка строк и обновление таблицы без использования курсора SQL

Question

Я пытаюсь создать хранимую процедуру MS SQL 2005, чтобы распределять запасы со склада для определенного компонента в заказе. Доступно несколько партий товара, и они должны использоваться в определенном порядке. Я могу циклически просматривать доступные запасы и распределять их до тех пор, пока заказ не будет выполнен, но я стараюсь думать больше о множестве, чем о последовательном, и избегать использования CURSOR.

Вот мой запрос, который позволяет получить складские запасы для определенного компонента заказа;

SELECT 
        STOCK.ComponentId,
        STOCK.StockId,
        STOCK.ExpiryDate, 
        STOCK.BatchNo, 
        STOCK.StockQty, 
        ORDER_ITEMS.OrderQty        
    FROM 
        STOCK 
        JOIN ORDER_ITEMS ON ORDER_ITEMS.ComponentId = STOCK.ComponentId 
    WHERE 
        STOCK.WarehouseId = @WarehouseId 
        AND STOCK.StockQty > 0
        AND ORDER_ITEMS.OrderItemId = @OrderItemId

Я поместил это во временную таблицу или создал CTE с запросом и применил предложение ORDER BY, чтобы отсортировать запас по мере необходимости. Это дает мне набор результатов, как:

ComponentId | StockId | ExpiryDate | BatchNo  | StockQty | OrderQty
-------------------------------------------------------------------
359         | 3107    | 2013-10-01 | 132435-1 | 20       | 50
359         | 3215    | 2013-10-01 | 154558-1 | 100      | 50
359         | 3216    | 2014-01-01 | 154689-1 | 100      | 50

Что мне нужно сделать, это вставить записи в STOCK_ALLOCATED таблица с использованием столько партий товара, сколько необходимо для выполнения заказа. В приведенном выше примере я бы использовал все 20 из первого ряда, а затем нужно 30 из второго ряда.

Это должно было бы вставить две записи в STOCK_ALLOCATED стол с OrderItemId, StockId и количество (20 и 30) для двух использованных партий, а также уменьшить количество запаса в STOCK стол соответственно.

Предполагая, что необходимые транзакции имеются для постоянного ведения таблиц запасов, есть ли способ выполнить вставки и обновления без использования CURSOR для циклического прохождения и отслеживания того, сколько акций я уже выделил и сколько мне еще нужно?

Answer 1

Это тестовый образец, который будет работать для вас:

SELECT  
    StockID,
    Quantity
FROM (
    SELECT  
        StockID,
        CASE 
            WHEN OrderQty - PreviousQty <= 0 THEN 0
        ELSE 
                CASE 
                    WHEN OrderQty - PreviousQty <= Stock 
                    THEN OrderQty - PreviousQty 
                ELSE
                    Stock 
                END
        END Quantity
    FROM (
        SELECT  
            a.StockID,
            a.Stock,
            a.OrderQty,
            a.OrderByField,
            ISNULL(SUM(b.Stock), 0) PreviousQty
        FROM    
            @Table a
            LEFT JOIN @Table b ON a.OrderByField > b.OrderByField
        GROUP BY a.StockID,
            a.Stock,
            a.OrderQty,
            a.OrderByField
        ) Orders
    ) Orders
WHERE   Quantity > 0

По сути, вам нужно будет объединить результаты, которые вы получаете сами, чтобы вы могли суммировать количества предыдущих зарезервированных количеств и вычесть это из StockQty, чтобы определить, что вам еще нужно выполнить.

Однако для этого вам понадобится уникальный OrderByField, чтобы вы могли точно отфильтровать предыдущие значения StockQty. Если у вас нет сохраненного значения, которое вы можете использовать, вы можете получить его с помощью ROW_NUMBER() OVER, Если это так, дайте мне знать, если вам нужна помощь с этим.