Split Records by Effective Date-CodePudding

I'm working on an SQL Server database trying to split records based on effective dates and now on a stall on how to split them.

I have 2 tables: Subscription and Effective_Rates as follows:

Subscription

ID    |  Date_From   |  Date_To     |   Cost
------ -------------- -------------- ------------
1001  |  2020-01-01  |  2020-12-31  |  2000.00
1002  |  2020-05-01  |  2021-04-30  |  3500.00

Effective_Rates

ID    |  Effective_From   |  Rate  
------ ------------------- --------
1001  |  2020-01-01       |  10.0
1002  |  2020-08-01       |  12.0
1003  |  2021-01-01       |  15.0

I need to join these two tables and split the records by rate based on effective date and looking for the result as follows:

ID    |  Date_From   |  Date_To     |    Cost     | Rate
------ -------------- -------------- ------------- --------
1001  |  2020-01-01  |  2020-07-31  |  2000.00    | 10.0
1001  |  2020-08-01  |  2020-12-31  |  2000.00    | 12.0
1002  |  2020-05-01  |  2020-07-31  |  3500.00    | 10.0
1002  |  2020-08-01  |  2020-12-31  |  3500.00    | 12.0
1002  |  2021-01-01  |  2021-04-30  |  3500.00    | 15.0

Again, appreciate your help

CodePudding user response：

WITH SUBSCRIPTION(ID,DATE_FROM,DATE_TO,COST) AS
(
  SELECT 1001  ,  '2020-01-01'  ,  '2020-12-31'  ,  2000.00 UNION ALL
  SELECT 1002  ,  '2020-05-01'  ,  '2021-04-31'  ,  3500.00
),
Effective_Rates(ID , Effective_From, Rate )AS
(
  SELECT 1001  ,  '2020-01-01'       ,  10.0 UNION ALL
  SELECT 1002  ,  '2020-08-01'       ,  12.0 UNION ALL
  SELECT 1003  ,  '2021-01-01'       ,  15.0
)

SELECT S.ID,S.DATE_FROM,S.DATE_TO,S.COST,X.Rate,X.Effective_From
 FROM SUBSCRIPTION AS S
CROSS APPLY
 (
   SELECT E.RATE,E.Effective_From
   FROM Effective_Rates AS E
   WHERE E.Effective_From BETWEEN S.DATE_FROM AND S.DATE_TO
)X
ORDER BY S.ID;

I am afraid it is not suitable completely, but hope can be useful

CodePudding user response：

Intervals a and b intersection condition is a.start<=b.end and b.start<=a.end. So intervals of interest can be found with the query

with eri as(
  select ID, Effective_From, Rate
   , dateadd(dd, -1, lead(Effective_From) over(order by Effective_From) ) eff_till
  from Effective_Rates
)   
SELECT S.ID 
   , case when S.DATE_FROM <= eri.Effective_From then eri.Effective_From else S.DATE_FROM end d_from
   , case when S.DATE_TO <= eri.eff_till or eri.eff_till is null then S.DATE_TO else eri.eff_till end d_to
   , S.COST, eri.Rate, eri.Effective_From
FROM SUBSCRIPTION AS S
JOIN eri on eri.Effective_From <=  S.DATE_TO and (s.DATE_FROM < eri.eff_till or eri.eff_till is null)
ORDER BY S.ID, d_from;

db<>fiddle