sql server - What is the most effcient way to replace values in a specific column of a table for this specific scenario?

Question

I am using SQL Server 2014 and I have a table in my database called t1 (extract of only 2 columns shown below):

 ResaID       StayDate
 100          2020-02-03
 100          2020-02-04
 100          2020-02-05
 120          2020-04-06
 120          2020-04-07
 120          2020-04-08
 120          2020-04-09
 120          2020-04-10

I need to change the dates in the StayDate column based on the following information (extract shown exactly as provided):

 ID        StartDate       EndDate
 100       2020-06-04      2020-06-06
 120       2021-03-01      2021-03-05

I have started writing my T-SQL query as follows (but it is getting quite tedious as I have to do it for more than 100 ResaID!):

USE MyDatabase

UPDATE t1

SET StayDate = CASE WHEN ResaID = 100 and StayDate = '2020-02-03' THEN '2020-06-04'
WHEN ResaID = 100 and StayDate = '2020-02-04' THEN '2020-06-05'
WHEN ResaID = 100 and StayDate = '2020-02-05' THEN '2020-06-06'
...    

ELSE StayDate

END

Is there a more efficient way to tackle this problem?

Answer 1

You can use recursive approach :

with r_cte as (
     select id, convert(date, startdate) as startdate, convert(date, enddate) as enddate
     from ( values (100, '2020-06-04', '2020-06-06'), 
                   (120, '2021-03-01', '2021-03-03')
          ) t(id, startdate, enddate)
     union all
     select id, dateadd(day, 1, startdate), enddate
     from cte c
     where startdate < enddate
), r_cte_seq as (
     select r.cte.*, row_number() over(partition by id order by startdate) as seq
     from r_cte 
), cte_seq as (
     select t1.*, row_number() over (partition by ResaID order by staydate) as seq
     from t1
)
update cs
       set cs.staydate = rc.startdate
from cte_seq cs inner join
     r_cte_seq rc
     on rc.id = cs.ResaID and rc.seq = cs.seq;