时间戳

首页 > 解决方案 > 仅获取行，直到达到 qty total

sql - 仅获取行，直到达到 qty total

问题描述

我有一个订单表 (product, qty_required) 和一个库存/仓位表 (product, bin_location, qty_free)，它是 one->many（一个产品可能存储在多个 bin 中）。拜托，拜托（非常拜托！）有人知道如何：在生成拣货报告时，我只想退回满足订单要求的每个订购产品的前 x 个箱子。例如，订单需要产品“ABC”，数量 10 产品“ABC”位于以下位置（使用 FIFO 规则列出，因此最早的在前）：LOC1、3 个空闲 LOC2、4 个空闲 LOC3、6 个空闲 LOC4、18 个空闲 LOC5 , 2 免费

所以。在报告中，我只想查看前 3 个位置，因为它们的总数（13 个）满足 10 个的订单数量......即：LOC1、3 LOC2、4 LOC3、6

标签： sqlcrystal-reports

解决方案

用于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

推荐阅读