Fill missing values in a ratio table based on ProvinceNo(Branch)?

Question

IF OBJECT_ID('tempdb..#ReflectionRatio') IS NOT NULL DROP TABLE #ReflectionRatio
CREATE TABLE #ReflectionRatio(
    [ReflectionRatioKey] [int] IDENTITY(1,1) NOT NULL,
    [StartDate] [date] NULL,
    [ProvinceNo] [nvarchar](3) NULL,
    [Rate] [float] NULL,
    [Period] [nvarchar](10) NULL
)

INSERT INTO #ReflectionRatio VALUES
(1,'2005-01-01','712',0.0002,'2005-01'),
(2,'2005-01-01','750',0.0661,'2005-01'),
(3,'2019-06-01','712',0.000114,'2019-06'),
(4,'2019-06-01','750',0.05972,'2019-06'),
(5,'2020-05-31','712',0.0002,'2020-05'),
(6,'2020-05-01','750',0.0661,'2020-05'),
(7,'2023-05-02','712',0.000242,'2023-05'),
(8,'2023-05-02','750',0.069265,'2023-05')

I need to fill in missing values according to rules defined below: Dates period to be filled in : 2005-01-01 / 2023-12-31

-- Rule.1: If a ratio is missing for a specific ProvinceNo, then take the previous most recent one:

For example: For 750 ProvinceNo and From 2019-07 period up to 2020-05, Record should be:

(IDENTITYCOLUMN,'2019-07-01','750',0.05972,'2019-07'),
(IDENTITYCOLUMN,'2019-08-01','750',0.05972,'2019-08'),
(IDENTITYCOLUMN,'2019-09-01','750',0.05972,'2019-09'),
........
........
(IDENTITYCOLUMN,'2020-04-01','750',0.05972,'2020-04')

--Rule.2: All ratio values in a province has 0.0 ,then take all of the previous province ratios.

Note: In fact, I have many provinces, not just 712 and 750!

I tried to solve it using window functions (LEAD and LAG) but I only managed to fetch the first next record and previous record.

ProvinceNo-750 and From 2019-07 period up to 2023-12, Records should be:

(IDENTITYCOLUMN,'2019-07-01','750',0.05972,'2019-07'),
(IDENTITYCOLUMN,'2019-08-01','750',0.05972,'2019-08'),
(IDENTITYCOLUMN,'2019-09-01','750',0.05972,'2019-09'),
........
........
(IDENTITYCOLUMN,'2020-04-01','750',0.05972,'2020-04')
(IDENTITYCOLUMN,'2020-05-01','750',0.0661,'2020-05')
(IDENTITYCOLUMN,'2020-06-01','750',0.0661,'2020-06')
.........
.........
(IDENTITYCOLUMN,'2023-04-01','750',0.0661,'2023-04')
(IDENTITYCOLUMN,'2023-05-02','750',0.069265,'2023-05')
(IDENTITYCOLUMN,'2023-06-02','750',0.069265,'2023-06')
.........
.........
(IDENTITYCOLUMN,'2023-12-02','750',0.069265,'2023-12')

I appreciate any help, Thanks!

Answer 1

Here is my stab it. Let me know if it helps.

;with cte_dates as (
    select convert(date, '2005-01-01') as data_date
    union all
    select dateadd(mm,1, data_date) 
    from cte_dates
    where data_date < '2023-12-01'
), cte_max_dates as (
    select c.data_date, r.StartDate, r.ProvinceNo, r.Rate 
    from cte_dates c 
        cross apply (select StartDate, ProvinceNo, Rate 
                    from #ReflectionRatio rr
                    where rr.StartDate = ( select max(StartDate) 
                                            from #ReflectionRatio ri 
                                            where ri.StartDate <= data_date 
                                            and ri.ProvinceNo = rr.ProvinceNo 
                                            and ri.Rate > 0 )
                                        ) r
)
select c.data_date, c.ProvinceNo, case when ISNULL(r.Rate,0) = 0 then c.Rate else r.Rate end Rate, convert(varchar(7),c.data_date, 20) Period 
from cte_max_dates c left join #ReflectionRatio r on r.StartDate = c.data_date and r.ProvinceNo = c.ProvinceNo  
order by c.data_date
option (maxrecursion 0)