SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table field_updates (
  Id integer primary key,
  ThingId integer not null, -- references things
  PreviousValue nvarchar(255) not null check(PreviousValue in ('Available', 'Covered', 'Dispatched', 'In-Transit')),
  CurrentValue nvarchar(255) not null check(CurrentValue in ('Available', 'Covered', 'Dispatched', 'In-Transit')),
  DateCreated date not null
);
insert into field_updates values
  (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'),

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

select * from field_updates;


Id
ThingId
PreviousValue
CurrentValue
DateCreated


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

5
2
Covered
Dispatched
2024-03-04

6
2
Dispatched
In-Transit
2024-03-05


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


Id
ThingId
PreviousValue
CurrentValue
DateCreated
rownum


3
1
Available
Covered
2024-03-03
1

1
1
Available
Covered
2024-03-01
2


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


Id
ThingId
PreviousValue
CurrentValue
DateCreated
rownum


4
1
Covered
Dispatched
2024-03-04
1

2
1
Covered
Available
2024-03-02
2

5
2
Covered
Dispatched
2024-03-04
1


  select distinct thingid
  from field_updates


thingid


1

2


with currently_covered as (
  select
  *,
  row_number() over(
    partition by ThingId
    order by DateCreated desc
  ) as rownum
  from field_updates
  where CurrentValue = 'Covered'
), previously_covered as (
  select
  *,
  row_number() over(
    partition by ThingId
    order by DateCreated desc
  ) as rownum
  from field_updates
  where PreviousValue = 'Covered'
), distinct_things as (
  select distinct thingid
  from field_updates
)
select
  dt.thingid, *
from distinct_things dt
left join currently_covered cc on dt.thingid = cc.thingid and cc.rownum = 1
left join previously_covered pc on dt.thingid = pc.thingid and pc.rownum = 1


thingid
thingid
Id
ThingId
PreviousValue
CurrentValue
DateCreated
rownum
Id
ThingId
PreviousValue
CurrentValue
DateCreated
rownum


1
1
3
1
Available
Covered
2024-03-03
1
4
1
Covered
Dispatched
2024-03-04
1

2
2
null
null
null
null
null
null
5
2
Covered
Dispatched
2024-03-04
1


with currently_covered as (
  select
  *,
  row_number() over(
    partition by ThingId
    order by DateCreated desc
  ) as rownum
  from field_updates
  where CurrentValue = 'Covered'
), previously_covered as (
  select
  *,
  row_number() over(
    partition by ThingId
    order by DateCreated desc
  ) as rownum
  from field_updates
  where PreviousValue = 'Covered'
), distinct_things as (
  select distinct thingid
  from field_updates
)
select
  dt.thingid, coalesce(cc.id, pc.id) as id
from distinct_things dt
left join currently_covered cc on dt.thingid = cc.thingid and cc.rownum = 1
left join previously_covered pc on dt.thingid = pc.thingid and pc.rownum = 1


thingid
id


1
3

2
5
Id	ThingId	PreviousValue	CurrentValue	DateCreated
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
5	2	Covered	Dispatched	2024-03-04
6	2	Dispatched	In-Transit	2024-03-05