I am trying to generate date and time between two records for a particular id for some reporting for hourly level. Idea is to bring any record that is even unchanged or changed at date and hourly basis.

I was able to generate the dates between two updates as shown in the below example. The first record on 31/05/2024 has text1 and the next update is on 12/06/2024 as text2222. I used the lead function to capture the eff_end date from the eff_start date and joined date dimension table on DATE BETWEEN date(EFF_FROM) AND date(EFF_TO) and got the below results. please refer the table below.

But,I still have challenges to drill down to hourly level. i.e. I need to explode these into hourly records starting from 31/05/2024 05 hour till current_date hour for each hour (00 till 23) and for all dates in between. please refer the last table for sample.

I tried joining the time dimension with this but unable to get proper output. Any help in this regards please?

SELECT 
TO_TIMESTAMP_NTZ(EFF_FROM) ,TO_TIMESTAMP_NTZ(EFF_TO) ,
D.DATE AS DATE_SNS,
other columns...
FROM 
input_table T
JOIN DIM_DATE D ON D.DATE BETWEEN date(T.EFF_FROM) AND date(T.EFF_TO)
JOIN dim_time t on t.hour BETWEEN  HOUR(W.EFF_FROM) AND HOUR(TO_TIMESTAMP_NTZ(current_timeSTAMP())) -- > this join didnt work as expected.