Search code examples
sqlsql-serverself-join

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

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/ .

Solution

  • 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)