比较表A中一列和表b中另一列的值

Question

我需要在预期的输出中创建一个NeedDate列。我将比较表B中的QtyShort和表a中的QtyReceive。

在预期的输出中，如果QtyShort = 0, NeedDate = MaltDueDate.

对于表A的第一行，如果0 <QtyShort(表B)><= QtyReceive (=6)， NeedDate = 10/08/2021(表A中的DueDate).

If 6<= 10 (QtyReceive)，移动到第二行，NeedDate = 10/22/2021 (DueDate from Table A)。

如果10<= 20 (QtyReceive)，移至第三行，NeedDate = 02/01/2022 (DueDate from Table A)。

If QtyShort>QtyReceive (=20)， NeedDate = 09/09/9999.

这应该在循环中继续，直到表B上的最后一行被比较完

我们怎么能这样做呢?任何帮助都将不胜感激。提前感谢!

表

Item    DueDate      QtyReceive
A1      10/08/2021       6
A1      10/22/2021       10
A1      02/01/2022       20

表B

Item    MatlDueDate    QtyShort
A1      06/01/2022         0
A1      06/02/2022         0
A1      06/03/2022         1
A1      06/04/2022         2
A1      06/05/2022         5
A1      06/06/2022         7
A1      06/07/2022         10
A1      06/08/2022         15  
A1      06/09/2022         25

预期输出:

Item    MatlDueDate    QtyShort     NeedDate
A1      06/01/2022        0        06/01/2022
A1      06/02/2022        0        06/02/2022
A1      06/03/2022        1        10/08/2021
A1      06/04/2022        2        10/08/2021
A1      06/05/2022        5        10/08/2021
A1      06/06/2022        7        10/22/2021
A1      06/07/2022        10       10/22/2021
A1      06/08/2022        15       02/01/2022 
A1      06/09/2022        25       09/09/9999

Answer 1

用OUTER APPLY()算符从TableA中找出能够满足QtyShort的最小DueDate

select b.Item, b.MatlDueDate, b.QtyShort,
NeedDate = case when b.QtyShort = 0
then b.MatlDueDate
else isnull(a.DueDate, '9999-09-09')
end
from   TableB b
outer apply
(
select DueDate = min(a.DueDate)
from   TableA a
where  a.Item = b.Item
and    a.QtyReceive >= b.QtyShort
) a

结果:

NeedDate2022-06-012022-06-022021-10-082021-10-082021-10-082021-10-222021-10-222022-02-019999-09-09