How to repeat previous row values until another field changes

I am trying to repeat a value from one row if there is no change between a column value in the row before. Thanks in advance!

create table #results (
    [Floor] varchar(20)
    ,Room varchar(20)
    ,CheckInTime datetime

)

INSERT INTO #results
  
VALUES
  ('Floor1','Room1','2020-01-01 12:00:00'),
  ('Floor1','Room2','2020-01-05 19:00:00'),
  ('Floor1','Room3','2020-01-20 08:02:00'),
  ('Floor2','Room1','2020-01-23 19:32:00'),
  ('Floor1','Room1','2020-02-01 20:00:00')

And the expected result I am looking for is

in the “WantedValue” column. So if Floor is the same as the row before it, use the CheckInTime value from the row before. But this needs to carry down through multiple rows potentially.

Answer

I am interpreting the question as you want the minimum date/time every time the floor changes.

Use lag() and a cumulative sum to define the groups. Then use a window function to “spread” the minimum value:

select r.*,
       min(checkintime) over (partition by grp order by checkintime) as wantedvalue
from (select r.*,
              sum(case when prev_floor = floor then 0 else 1 end) over (order by checkintime) as grp
      from (select r.*,
                   lag(floor) over (order by checkintime) as prev_floor
            from results r
           ) r
     ) r
order by checkintime;

Here is a db<>fiddle.