Блог

У меня есть таблица заказов (product, qty_required) и таблица расположения запасов / ячеек (product, bin_location, qty_free), которая представляет собой один-> много (продукт может храниться в нескольких ячейках). Пожалуйста, пожалуйста (очень пожалуйста!) Кто-нибудь знает, как: при создании отчета о комплектации я хочу вернуть только первые x ячеек для каждого заказанного продукта, КОТОРЫЙ УДОВЛЕТВОРЯЕТ qty_required в заказе. Например, для заказа требуется продукт ‘ABC’, КОЛ-ВО 10 Продукт ‘ABC’ находится в следующих расположениях (они перечислены с использованием правил FIFO, поэтому самые старые сначала): LOC1, 3 бесплатных LOC2, 4 бесплатных LOC3, 6 бесплатных LOC4, 18 бесплатных LOC5, 2 бесплатных

итак. в отчете я бы хотел видеть ТОЛЬКО первые 3 местоположения, так как общее их количество (13) соответствует количеству заказа 10… Т.Е.: LOC1, 3 LOC2, 4 LOC3, 6

Используется sum(qty_free) over(partition by product order by placement_date desc, bin_location) для вычисления текущей суммы и фильтрации строк по вашему порогу во внешнем запросе ( select from select ). Добавлено местоположение для того, чтобы исключить сумму всех местоположений, где размещение было в тот же день.

 with s as (
  select st.*,
    sum(qty_free) over(partition by product order by placement_date asc, bin_location) as rsum
  from stock st
)
select
  o.product,
  s.bin_location,
  s.qty_free,
  o.qty_requested
from orders o
  left join s
    on o.product = s.product
      and s.rsum <= o.qty_requested
  

UPD: поскольку оказалось, что ваша версия SQL Server настолько старая, что в ней нет аналитической функции, вот еще один менее эффективный способ сделать это (возможно, нужны некоторые исправления, не тестировался на реальных данных).
И поиграйте с некоторыми настройками.

 with ord_key as (
  select stock.*,
    /*Generate order key for FIFO*/
    row_number() over(order by
      placement_date desc,
      bin_location asc
    ) as sort_order_key
  from stock
)
, rsum as (
  /*Calculate sum of all the items before current*/
  select
    b.product,
    b.bin_location,
    b.placement_date,
    b.qty_free,
    coalesce(sum(sub.item_sum), 0) as rsum
  from ord_key as b
    left join (
      /*Group by partition key and orderby key*/
      select
        product,
        sort_order_key,
        sum(qty_free) as item_sum
      from ord_key
      group by 
        product,
        sort_order_key
    ) as sub
      on b.product = sub.product
        and b.sort_order_key > sub.sort_order_key
  group by
    b.product,
    b.bin_location,
    b.placement_date,
    b.qty_free
)
, calc_quantities as (
  select
    o.product,
    s.placement_date,
    s.bin_location,
    s.qty_free,
    s.rsum,
    o.qty_requested,
    case
      when o.qty_requested > s.rsum   s.qty_free
      then s.qty_free
      else s.rsum   s.qty_free - o.qty_requested
    end as qty_to_retrieve
  from orders o
    left join rsum s
      on o.product = s.product
        and s.rsum < o.qty_requested
)
select
  s.*,
  qty_free - qty_to_retrieve as stock_left
from calc_quantities s
order by
  product,
  placement_date desc,
  bin_location desc