I need to create a NeedDate column in the expected output. I will compare the QtyShort from Table B with QtyReceive from table A.

In the expected output, if QtyShort = 0, NeedDate = MaltDueDate.

For the first row of table A, if 0 < QtyShort (in Table B) <= QtyReceive (=6), NeedDate = 10/08/2021 (DueDate from Table A).

If 6 < QtyShort <= 10 (QtyReceive), move to the second row, NeedDate = 10/22/2021 (DueDate from Table A).

If 10 < QtyShort <= 20 (QtyReceive), move to the third row, NeedDate = 02/01/2022 (DueDate from Table A).

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

This should continue in a loop until the last row on table B has been compared

How could we do this? Any help will be appreciated. Thank you in advance!

Table A

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

Table 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

Expected Output:

 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

CodePudding user response：

Use OUTER APPLY() operator to find the minimum DueDate from TableA that is able to fulfill the QtyShort

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

Result:

db<>fiddle demo