Return values from Table A to Table B-CodePudding

I need help. Hi guys, I have 2 tables and I want to return the FIRST DueDate and QtyReceive from Table A to Table B if RunningTotalQtyReceive (from Table A) >= RunningTotalQtyShort (from Table B). It returns NULL if QtyReceive/TotalRunningQtyReceive can't cover QtyShort/TotalRunningQtyShort. The dataset has been ordered by Date. RunningTotalQtyReceive and RunningTotalQtyShort have been calculated by Window Function.

SUM(QtyReceive) OVER(PARTITION BY Item ORDER BY DueDate)
SUM(QtyShort) OVER(PARTITION BY Item ORDER BY MatlDueDate)

Table A:

Item     DueDate         QtyReceive   RunningTotalQtyReceive
 A1     2021-10-08            6                6
 A1     2021-10-22            5                11
 A1     2022-02-01            9                20

Table B:

Item    MatlDueDate     QtyShort    RunningTotalQtyShort
 A1      2022-06-01        0                0
 A1      2022-06-03        1                1
 A1      2022-06-04        2                3
 A1      2022-06-05        4                7
 A1      2022-06-06        8                15
 A1      2022-06-07        5                20
 A1      2022-06-08        3                23
 A1      2022-06-09        10               33

Expected Output:

Item    MatlDueDate QtyShort    RunningTotalQtyShort    RunningTotalQtyReceive  DueDate    QtyReceive
 A1     2022-06-01     0                 0                       6             2021-10-08      6
 A1     2022-06-03     1                 1                       6             2021-10-08      6
 A1     2022-06-04     2                 3                       6             2021-10-08      6
 A1     2022-06-05     4                 7                       11            2021-10-22      5
 A1     2022-06-06     8                 15                      20            2022-02-01      9
 A1     2022-06-07     5                 20                      20            2022-02-01      9
 A1     2022-06-08     3                 23                      NULL            NULL          NULL
 A1     2022-06-09     10                33                      NULL              NULL        NULL

This is my script to return the expected output:

SELECT 
     b.*, 
     c.RunningTotalQTyReceive, 
     c.DueDate, 
     c.QtyReceive
FROM TableB b 
OUTER APPLY (SELECT TOP 1 
                a.RunningTotalQTyReceive,
                FIRST_VALUE(DueDate) OVER(ORDER BY DueDate) AS DueDate, 
                a.QtyReceive
             FROM TableA a
             WHERE a.item = b.item
             AND a.RunningTotalQtyReceive >= b.RunningTotalQtyShort
             ) c

The problem is that this is "Triangular" Join (https://www.sqlservercentral.com/articles/hidden-rbar-triangular-joins#:~:text=A Triangular Join is nothing,rows spawned are nearly trivial) which slows down the query execution time. It took me 35 mins to 1 hour to finish the execution with the 200,000-row dataset. Could you please help me with any idea to reduce the time execution? Any other joins or user-defined functions? Thank you a lot! Any help will be appreciated. Please let me know if you need any clarification on my question.

Create TableA:

CREATE TABLE TableA
(
Item varchar(2),
DueDate date,
QtyReceive int,
RunningTotalQtyReceive int
);

INSERT INTO TableA values
('A1',      '10/08/2021',       6,       6),
('A1',      '10/22/2021',       5,      11),
('A1',      '02/01/2022',       9,      20);

Create TableB:

CREATE TABLE TableB
(
Item varchar(2),
MatlDueDate date,   
QtyShort int,
RunningTotalQtyShort int,
);

INSERT INTO TableB values
('A1',      '06/01/2022',         0,         0),
('A1',      '06/03/2022',         1,         1),
('A1',      '06/04/2022',         2,         3),
('A1',      '06/05/2022',         4,         7),
('A1',      '06/06/2022',         8,         15),
('A1',      '06/07/2022',         5,         20),
('A1',      '06/08/2022',         3,         23),
('A1',      '06/09/2022',         10,        33);

CodePudding user response：

You don't need to store the values of RunningTotalQtyReceive and RunningTotalQtyShort, since they can be calculated from the input tables. Try the following to get the desired results:

With CTE1 AS
(
  Select Item, DueDate, QtyReceive,  
         SUM(QtyReceive) OVER(PARTITION BY Item ORDER BY DueDate) AS RunningTotalQtyReceive
  From TableA
),
CTE2 AS
(
 Select Item, MatlDueDate, QtyShort,
        SUM(QtyShort) OVER(PARTITION BY Item ORDER BY MatlDueDate) AS RunningTotalQtyShort
 From TableB
)

Select B.Item, B.MatlDueDate, B.QtyShort, B.RunningTotalQtyShort,
       A.RunningTotalQtyReceive, A.DueDate, A.QtyReceive
From CTE2 B
Left Join
(
  Select Item, DueDate, QtyReceive, RunningTotalQtyReceive, 
         LAG(RunningTotalQtyReceive, 1, 0) Over (Partition By Item Order By DueDate) AS lrtqr
  From CTE1
) A

On B.RunningTotalQtyShort <=  A.RunningTotalQtyReceive and B.RunningTotalQtyShort >=  A.lrtqr
And B.Item = A.Item

See a demo from db<>fiddle.

LAG(RunningTotalQtyReceive, 1, 0) Over (Partition By Item Order By DueDate) AS lrtqr the LAG function is used to find the previous value of RunningTotalQtyReceive for each item according to the date order. Now we can define a range of RunningTotalQtyReceive that belongs to a DueDate, which is between the current RunningTotalQtyReceive and the previous one. For the first row, the LAG value will be 0 (the default value) since no previous value is existed for this value.

The LEFT JOIN returns all rows from the left table (CTE2), and the matching rows from the right table (CTE1). The result is null from CTE1 if there is no match. The matching occured when CTE2.RunningTotalQtyShort is between CTE1.RunningTotalQtyReceive (current value) and CTE1.lrtqr (previous value).