从自下而上方法获取值

Question

我有两个名为"Purchase"one_answers"ClosingStock"的表

附加了dbfiddle中的示例表以便于快速操作。

表格：采购表格

+----------+--------------+-----+-----------+
| ItemName | PurchaseDate | QTY | CostPrice |
+----------+--------------+-----+-----------+
| ItemA    | 2022-03-14   |  20 | 32.00     |
| ItemA    | 2022-04-28   |   7 | 30.00     |
| ItemA    | 2022-06-17   |  33 | 25.00     |
|          |              |     |           |
| ItemB    | 2022-05-16   |  65 | 50.00     |
+----------+--------------+-----+-----------+

表：关闭库存

+----------+--------------+
| ItemName | ClosingStock |
+----------+--------------+
| ItemA    |           35 |
| ItemB    |           60 |
+----------+--------------+

所需结果

+----------+--------------+------------+
| ItemName | ClosingStock | TotalValue |
+----------+--------------+------------+
| ItemA    |           35 |        885 |
| ItemB    |           60 |       3000 |
+----------+--------------+------------+

所需查询应返回基于自下而上方法的"TotalValue">

例如：

在我的示例数据集中，ItemA的ClosingQTY=35

要计算ItemA的TotalValue，它应该检查Purchase表中的最后一条记录(QTY列(。这是33。

因此，在35个ClosingQTY中，有33个具有价值(33*25(。对于项目A的剩余2个数量，它应该在采购中查找倒数第二个记录。所以应该是(2*30(。

数学表达式为(33*25(+(2*30(=885

因此，对于总共35个数量的项目A，总值将是885。类似于项目B等的计算。

https://dbfiddle.uk/plwwAGr6

Answer 1

您可以使用join，计算QTY的运行和，将其与ClosingStock进行比较，当运行和大于ClosingStock时，只从QTY中取一部分。然后将计算出的总量乘以价格。

with purchase(ItemName, PurchaseDate, QTY, CostPrice) as (
select 'ItemA', convert(date, '2022-03-14'), 20, 32.00 union all
select 'ItemA', convert(date, '2022-04-28'),  7, 30.00 union all
select 'ItemA', convert(date, '2022-06-17'), 33, 25.00 union all
select 'ItemB', convert(date, '2022-05-16'), 65, 50.00
)
, stock(ItemName, ClosingStock) as (
select 'ItemA', 35 union all
select 'ItemB', 60
)
select
itemname
, sum(TotalValue ) as TotalValue
from (
select
p.itemname
, case
/*Running total is less than stock - take purchase in full*/
when s.ClosingStock
>= sum(p.qty) over(
partition by p.itemname order by PurchaseDate desc
)
then p.qty
/*Otherwise if previous running total is less then stock - take only part*/
when s.ClosingStock - (
sum(p.qty) over(
partition by p.itemname order by PurchaseDate desc
) - p.qty
) > 0
then s.ClosingStock - (sum(p.qty) over(partition by p.itemname order by PurchaseDate desc) - p.qty)
/*When previous running total is greater than stock, then do not account this purchase*/
end * p.costprice as TotalValue 
, p.purchasedate
, s.closingstock
from purchase as p
join stock as s
on p.ItemName = s.ItemName
) as t
group by itemname

itemname	TotalValue
项目A	885.00
项目B	3000.00

两种解决方案都能工作(由IPTR和astentx(

IPTS的方法似乎更有效，因为它使用"；无界前置"；

select ItemName, ClosingQTY
,sum(case when rt <= ClosingQTY then QTY else ClosingQTY - (rt - QTY) end * CostPrice) 
from 
(
select 
c.ClosingQTY, 
p.*, 
sum(p.QTY) over(partition by p.ItemName order by p.PurchaseDate desc rows unbounded preceding) as rt 
from 
ClosingStock as c 
join Purchase as p on c.ItemName = p.ItemName
) as r 
where rt - QTY < ClosingQTY 
group by ItemName, ClosingQTY