Copying values down to next empty row with some conditions

Question

So I am using a specialized version of SQL where the transformations are either make tables or within the select statement. Cannot use variables or CTE as I have seen in some of the other examples on solving this issue.

Please see below for the created table and a sample of desired output https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f245276910804da864b63c1f39bf9677

(101, '3/08/22', 'Owner', '', 'Some One'),
(101, '3/06/22', 'Resolution Date', '3/09/22', '4/05/22'),
(102, '3/11/22', 'Owner', '', 'Some One'),
(102, '3/12/22', 'Resolution Date', '3/20/22', '3/25/22'),
(102, '3/13/22', 'Owner', 'Some One', 'New Person'),
(102, '3/15/22', 'Resolution Date', '3/28/22', '3/29/22'),
(102, '3/16/23', 'Owner', 'New Person', 'Newer Person'),
(102, '3/15/22', 'Resolution Date', '3/28/22', '3/29/22'),
(102, '3/16/23', 'Owner', 'Newer Person', 'Newbie Person'),
(102, '3/17/22', 'Resolution Date', '4/28/22', '3/29/22');

select * from t

CREATE TABLE desiredoutput (caseid int, date date, field varchar(50), changed_to varchar(50), changed_from varchar(50),
owner_beg varchar(50), owner_end varchar(50));

INSERT INTO desiredoutput VALUES 
(101, '3/08/22', 'Owner', '', 'Some One', '', 'Some One'),
(101, '3/06/22', 'Resolution Date', '3/09/22', '4/05/22', 'Some One', 'Some One'),
(102, '3/11/22', 'Owner', '', 'Some One', '', 'Some One'),
(102, '3/12/22', 'Resolution Date', '3/20/22', '3/25/22', 'Some One', 'Some One'),
(102, '3/13/22', 'Owner', 'Some One', 'New Person', 'Some One', 'New Person'),
(102, '3/15/22', 'Resolution Date', '3/28/22', '3/29/22', 'New Person', 'New Person'),
(102, '3/16/23', 'Owner', 'New Person', 'Newer Person', 'New Person', 'Newer Person'),
(102, '3/15/22', 'Resolution Date', '3/28/22', '3/29/22', 'Newer Person', 'Newer Person'),
(102, '3/16/23', 'Owner', 'Newer Person', 'Newbie Person', 'Newer Person', 'Newbie Person'),
(102, '3/17/22', 'Resolution Date', '4/28/22', '3/29/22', 'Newbie Person', 'Newbie Person');

The Top Table is how the data comes out of the source system without clear owners for each line so it is awkward as cannot see length of time passing between each owner.

The Bottom Table has the owner at the beginning and the end of the step as extra columns. This way creating an owner list for the casesids. With the skipping of lines and inconsistent placings of the owner tables, have not been able to use lag or lead to copy down to next empty rows

Any thoughts on how I could possible tackle this? Many thanks for reading!

edit one: I have tried all the CTE and Variables, it does not work in the sql window for the program.

I have also tried to iterate using lag or lead over partion by etc due to different row actions, it does not work. Have also tried a make table join against the main table with a lag or lead, but that also does not seem to work.

Answer 1

We can use CASE and LAG to get this result, without using a CTE or variables.

select
  caseid,
  date,
  field,
  changed_to,
  changed_from,
  case 
    when field = 'Owner' then '' 
    else lag(changed_from) over (partition by caseid order by rn,field) 
    end as owner_beg,
   case     
    when field = 'Owner' then lag(changed_from) over(partition by caseid, field order by rn)
    else lag(changed_from) over (partition by caseid order by rn,field) 
    end as owner_end
from
(select
  caseid,
  date,
  field,
  changed_to,
  changed_from,
  row_number() over (partition by caseid,field
                   order by date ) rn
from t) as s
order by
  caseid,
  rn,field
GO

caseid | date       | field           | changed_to   | changed_from  | owner_beg     | owner_end    
-----: | :--------- | :-------------- | :----------- | :------------ | :------------ | :------------
   101 | 2022-03-08 | Owner           |              | Some One      |               | null         
   101 | 2022-03-06 | Resolution Date | 3/09/22      | 4/05/22       | Some One      | Some One     
   102 | 2022-03-11 | Owner           |              | Some One      |               | null         
   102 | 2022-03-12 | Resolution Date | 3/20/22      | 3/25/22       | Some One      | Some One     
   102 | 2022-03-13 | Owner           | Some One     | New Person    |               | Some One     
   102 | 2022-03-15 | Resolution Date | 3/28/22      | 3/29/22       | New Person    | New Person   
   102 | 2023-03-16 | Owner           | New Person   | Newer Person  |               | New Person   
   102 | 2022-03-15 | Resolution Date | 3/28/22      | 3/29/22       | Newer Person  | Newer Person 
   102 | 2023-03-16 | Owner           | Newer Person | Newbie Person |               | Newer Person 
   102 | 2022-03-17 | Resolution Date | 4/28/22      | 3/29/22       | Newbie Person | Newbie Person

db<>fiddle here