Problem statement description :- I have two tables - table1 and table2 . table1 contains data of quantity buy of id=7 and table2 contain data of quantity sold of same id=7. Both table1 and table2 is sorted according to date i.e from oldest to latest date and every time only 1 quantity is buy or sold.

table1:

table2:-

-- SQL scrtpt of table1:

CREATE TABLE IF NOT EXISTS table1 (
    `date_buy` DATETIME,
    `id` INT,
    `qty_buy` INT,
    `rolling_sum_qty_buy` INT
);
INSERT INTO table1 VALUES
    ('2019-07-30 00:00:00',7,1,1),
    ('2019-10-20 00:00:00',7,1,2),
    ('2020-01-17 00:00:00',7,1,3),
    ('2020-02-15 00:00:00',7,1,4),
    ('2020-02-15 00:00:00',7,1,5),
    ('2020-02-15 00:00:00',7,1,6),
    ('2021-07-14 00:00:00',7,1,7),
    ('2021-09-19 00:00:00',7,1,8),
    ('2021-12-25 00:00:00',7,1,9),
    ('2021-12-30 00:00:00',7,1,10),
    ('2022-02-10 00:00:00',7,1,11),
    ('2022-03-15 00:00:00',7,1,12),
    ('2022-03-15 00:00:00',7,1,13),
    ('2022-06-14 00:00:00',7,1,14);

-- sql script of table2:

CREATE TABLE IF NOT EXISTS table2 (
    `date_sold` DATETIME,
    `id` INT,
    `qty_sold` INT,
    `rolling_sum_qty_sold` INT
);
INSERT INTO table2 VALUES
    ('2019-08-01 00:00:00',7,1,1),
    ('2019-09-15 00:00:00',7,1,2),
    ('2019-12-27 00:00:00',7,1,3),
    ('2020-02-01 00:00:00',7,1,4),
    ('2020-02-12 00:00:00',7,1,5),
    ('2021-07-25 00:00:00',7,1,6),
    ('2021-07-25 00:00:00',7,1,7),
    ('2021-08-28 00:00:00',7,1,8),
    ('2021-09-10 00:00:00',7,1,9),
    ('2021-09-12 00:00:00',7,1,10),
    ('2022-04-25 00:00:00',7,1,11);

-- Now, i want to join this two table on two condition

1. for every date i.e date_buy column in table1 i should get output where date i.e date_sold is greater than date_buy and i want first date i.e. date_sold which is greater than that particular date i.e date_buy.

2. i also want those rows from table1 in my output which does not get joined with table2 so that i can easily find out the remaining quantity because in table1 i have quantity buy and after joining with table2 i will get quantity sold, so the cases where i get null values then in that case i can assume that that much quantity is remaining.

--My output:- Earlier when there was no date issue then i was simply using left join to join table1 and table2 on rolling sum condition and where there was null cases i was taking sum of qty to get remaining qty but right now i have that condition too so i cant use rolling_sum_cond column directly in join condition.

-- query which i was using and output which i was getting earlier

select * from table1
left join table2 
on table1.rolling_sum_qty_buy=table2.rolling_sum_qty_sold

and to find out remaining quantity , i was using null condition query:-

 with cte as 
(
select * from table1
left join table2 
on table1.rolling_sum_qty_buy=table2.rolling_sum_qty_sold
)
select sum(qty_buy) as remaining_qty 
from cte
where cte.date_sold is null
 

-- my expectation now i have to use date condition also to get the output

-- Expected Output

-- Please help me to get the following output. Any help would be appreciated. I am using postgresql.

CodePudding user response：

That was a challenging one.

with recursive cte as
(
  select   t2.date_sold
          ,t2.rolling_sum_qty_sold
          ,true as is_match
          ,1 as last_rolling_sum_qty_buy                           
  from     t2 join t on t2.rolling_sum_qty_sold = t.rolling_sum_qty_buy
  where    t2.rolling_sum_qty_sold = 1 
        
   
  union all 
  
  select t2.date_sold
        ,t2.rolling_sum_qty_sold 
        ,t2.date_sold >= t.date_buy
        ,cte.last_rolling_sum_qty_buy   case when t2.date_sold >= t.date_buy then 1 else 0 end
  from   t2 
         join cte on cte.rolling_sum_qty_sold   1 = t2.rolling_sum_qty_sold
         join t   on t.rolling_sum_qty_buy = cte.last_rolling_sum_qty_buy   1
          
)

select  t.date_buy  
       ,t.id    
       ,t.qty_buy   
       ,t.rolling_sum_qty_buy   
       ,cte.date_sold   
       ,cte.rolling_sum_qty_sold
from    t left join cte on cte.last_rolling_sum_qty_buy = t.rolling_sum_qty_buy and is_match

Fiddle