Map multiple rows based on cumulative value to another reference row in the same table-CodePudding

I have SQL table as below:

PO	Delivery_Type	Scheduled_Line_Item	Date	Quantity
100	Scheduled Delivery	01	01/12/2021	100,000
100	Scheduled Delivery	02	07/12/2021	75,000
100	Actual Delivery	NULL	03/12/2021	50,000
100	Actual Delivery	NULL	12/12/2021	50,000
100	Actual Delivery	NULL	15/12/2021	20,000
100	Actual Delivery	NULL	31/12/2021	55,000

I am trying to map out the Actual Delivery items to particular Scheduled_Line_Item based on the sequence of delivery date, to produce result as below:

PO	Delivery_Type	Scheduled_Line_Item	Date	Delivered_Qty	Scheduled_Qty
100	Actual Delivery	01	03/12/2021	50,000	100,000
100	Actual Delivery	01	12/12/2021	50,000	100,000
100	Actual Delivery	02	15/12/2021	20,000	75,000
100	Actual Delivery	02	31/12/2021	55,000	75,000

As you can see from above, the first two rows are mapped to "Scheduled_Line_Item" "01" because the sequence of Date have fulfilled the first 100,000 of the quantity in the Scheduled Delivery. Once the "Actual Delivery" has fulfilled the first "Schedule Delivery", then only we move to the next "Schedule Delivery" which is why the next two rows are mapped to "Scheduled_Line_Item" "02".

I found this method but unable to apply it to my requirements https://sqlsunday.com/2019/09/16/calculating-invoice-settlements/ .

CodePudding user response：

Assuming that PO Date is unique for 'Actual Delivery' you can match running totals excluding repeated rows

with sd as (
   select *, sum(Quantity) over(partition by PO order by date) s
   from tbl
   where Delivery_Type ='Scheduled Delivery'
),
ad  as (
  select *, sum(Quantity) over(partition by PO order by date) s
   from tbl
   where Delivery_Type ='Actual Delivery'
)
select top(1) with ties sd.*, ad.*
from sd
left join ad on ad.PO=sd.PO and sd.s>= ad.s 
order by row_number() over(partition by ad.PO, ad.Date order by sd.date)