SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table field_updates (
  Id integer not null identity(1,1) primary key,
  ThingId integer not null, -- references things
  PreviousValue nvarchar(255),
  CurrentValue nvarchar(255) not null,
  DateCreated date not null
);
insert into field_updates(thingid, previousvalue, currentvalue, datecreated) values
  (1, 'Available', 'Covered', '2024-03-01'),
  (1, 'Covered', 'Available', '2024-03-02'),
  (1, 'Available', 'Covered', '2024-03-03'),
  (1, 'Covered', 'Dispatched', '2024-03-04'),

  (2, 'Covered', 'Dispatched', '2024-03-04'),
  (2, 'Dispatched', 'In-Transit', '2024-03-05');
6 rows affected

with distinct_things as (
  select distinct thingid
  from field_updates
), first_values as (
  select
    thingid, previousvalue, datecreated,
    row_number() over (
      partition by thingid
      order by datecreated asc
    ) as rownum
  from field_updates
)
select dt.thingid, null, fv.previousvalue, dateadd(day, -1, fv.datecreated) as datecreated
from distinct_things dt
left join first_values fv on dt.thingid = fv.thingid and rownum = 1


thingid
(No column name)
previousvalue
datecreated


1
null
Available
2024-02-29

2
null
Covered
2024-03-03


with distinct_things as (
  select distinct thingid
  from field_updates
), first_values as (
  select
    thingid, previousvalue, datecreated,
    row_number() over (
      partition by thingid
      order by datecreated asc
    ) as rownum
  from field_updates
)
insert into field_updates(thingid, previousvalue, currentvalue, datecreated)
select dt.thingid, null, fv.previousvalue, dateadd(day, -1, fv.datecreated)
from distinct_things dt
left join first_values fv on dt.thingid = fv.thingid and rownum = 1
2 rows affected

select * from field_updates order by thingid, datecreated;


Id
ThingId
PreviousValue
CurrentValue
DateCreated


7
1
null
Available
2024-02-29

1
1
Available
Covered
2024-03-01

2
1
Covered
Available
2024-03-02

3
1
Available
Covered
2024-03-03

4
1
Covered
Dispatched
2024-03-04

8
2
null
Covered
2024-03-03

5
2
Covered
Dispatched
2024-03-04

6
2
Dispatched
In-Transit
2024-03-05


alter table field_updates drop column previousvalue;
select
  *,
  lag(currentvalue) over(
    partition by thingid
    order by datecreated
  ) as PreviousValue
from field_updates
order by thingid, datecreated;


Id
ThingId
CurrentValue
DateCreated
PreviousValue


7
1
Available
2024-02-29
null

1
1
Covered
2024-03-01
Available

2
1
Available
2024-03-02
Covered

3
1
Covered
2024-03-03
Available

4
1
Dispatched
2024-03-04
Covered

8
2
Covered
2024-03-03
null

5
2
Dispatched
2024-03-04
Covered

6
2
In-Transit
2024-03-05
Dispatched


with currently_covered as (
  select
  *,
  row_number() over(
    partition by ThingId
    order by DateCreated desc
  ) as rownum
  from field_updates
  where CurrentValue = 'Covered'
)
select *
from currently_covered
where rownum = 1


Id
ThingId
CurrentValue
DateCreated
rownum


3
1
Covered
2024-03-03
1

8
2
Covered
2024-03-03
1

Id	ThingId	PreviousValue	CurrentValue	DateCreated
7	1	null	Available	2024-02-29
1	1	Available	Covered	2024-03-01
2	1	Covered	Available	2024-03-02
3	1	Available	Covered	2024-03-03
4	1	Covered	Dispatched	2024-03-04
8	2	null	Covered	2024-03-03
5	2	Covered	Dispatched	2024-03-04
6	2	Dispatched	In-Transit	2024-03-05