Calculate differing accumulated storage charges over time?

I am working on a project for a wine importer, in which need to calculate the accumulated storage charges per SKU up to the delivery date.

I have data from the warehouse recording the Date_In and Date_Out so calculating DATEDIFF('day', Date_In, Date_Out) is fairly trivial. However every year the warehouse changes its prices, so when calculating the storage charges I need to split my Days_in_Stock across differing rates.

Input data with stock movement dates:

+----------------+------------+---------+-----+------------+------------+
| INVOICE_NUMBER | STOCK_CODE | CS_SIZE | QTY |  DATE_IN   |  DATE_OUT  |
+----------------+------------+---------+-----+------------+------------+
|           1001 | MP-BMB13   |       6 |   1 | 2019-03-01 | 2019-06-01 |
|           1002 | MP-BMB13   |       6 |   1 | 2019-03-01 | 2019-10-01 |
+----------------+------------+---------+-----+------------+------------+

Storage rates:

+----------------+-----------+-------------+
| DATE_EFFECTIVE | CASE_SIZE | COST_PER_WK |
+----------------+-----------+-------------+
| 2018-09-01     |         9 |      0.1375 |
| 2018-09-01     |         6 |     0.06875 |
| 2019-09-01     |         9 |      0.1425 |
| 2010-09-01     |         6 |     0.07125 |
+----------------+-----------+-------------+

My desired output is as follows:

+----------------+------------+---------------+--------------+
| INVOICE_NUMBER | STOCK_CODE | DAYS_IN_STOCK | STORAGE_COST |
+----------------+------------+---------------+--------------+
|           1001 | MP-BMB13   |            92 |         0.90 |
|           1002 | MP-BMB13   |           214 |         2.10 |
+----------------+------------+---------------+--------------+

DAYS_IN_STOCK is calculated with DATEDIFF('day', Date_In, Date_Out)

STORAGE_COST is COST_PER_WK * DAYS_IN_STOCK / 7

The issue I have is where as with Invoice 1002 the DAYS_IN_STOCK spans more than one charge rate. In which case the calculation needs to be something like:

COST_PER_WK (before 2019-09-01)* DAYS_IN_STOCK (before 2019-09-01) / 7
+ 
COST_PER_WK (since 2019-09-01)* DAYS_IN_STOCK (since 2019-09-01) / 7

Ideally the final query would be able to handle cases where the item has been in stock for more than 2 charge rate periods too.

Approaches attempted so far:

• Hard coding the query to find number of days before and after the rate change, but I will eventually need to use data going back 5 years and implement the same thing for a much more complicated delivery rate table.
• Trying to JOIN Date_In to Date_Effective, but I couldn’t get it to work, and have no idea how I would extend to 3 price rates.

I’m fairly stumped as to how to tackle this so any ideas would be very welcome. Currently using SQL Server 2017

Answer

This should give you an idea on how to solve this. I don’t want to deal with weeks, so I’ll phrase this answer in terms of days.

The basic idea is to add an end date to the rates table, then use join and aggregation:

with sr as (
      select r.*,
             lead(date_effective) over (partition by case_size) as date_end,
             cost_per_wk / 7.0 as cost_per_day
      from storage_rates r
     )
select i.*, sr.total_cost
from invoices i outer apply
     (select sum(datediff(day,
                          (case when i.date_in > sr.date_effective then i.date_in else sr.date_effective end),
                          (case when i.date_out < sr.date_eend then i.date_out else sr.date_end end)
                         ) * sr.cost_per_day
                 ) as total_cost
      from sr
      where i.cs_size = sr.cs_size and  -- I think the case size is relevant
            i.date_in < sr.date_end and
            i.date_out >= sr.date_effective
     ) sr;