EN VI

Sql - Query where alternate scenarios could take place?

Sql - Query where alternate scenarios could take place

I have a query where I am checking for the first instance of a status. The issue is that the status can vary in some circumstances.

For example I have records like the following and the correct record would be ID=12 because it is the first instance the item was actually Covered. 10 appears so, but then it changed to Available again so would not be the final position the record is set to Covered.

ID	PreviousValue	CurrentValue	DateCreated
10	Available	Covered	2024-03-01
11	Covered	Available	2024-03-02
12	Available	Covered	2024-03-03
13	Covered	Dispatched	2024-03-04

The other complexity is that in same cases I may have the following where there is no Available instance, it just starts as Covered and I would need to know that, that is the record I am looking for.

ID	PreviousValue	CurrentValue	DateCreated
10	Covered	Dispatched	2024-03-04
11	Dispatched	In-Transit	2024-03-05

I tried something like this, but it does not seem to return the correct record from the first recordset above.

select *
from loads_fieldupdates 
where theField = 'loadStatus'
and loadid = 261574
and (
        /* most common scenario */
        (previousFieldVal LIKE 'Available%' AND theFieldVal LIKE 'Covered%')
        OR
        /* case where load was duped so started as covered but not went back to available */
        (previousFieldVal LIKE 'Covered%' AND theFieldVal NOT LIKE 'Available%')
)
order by dateCreated desc

Solution:

I have a query where I am checking for the first instance of a status.

Seems like you're checking for the latest instance of a status.

It seems like the problem statement can be simplified to:

Find the latest row where CurrentValue is Covered.
If no such row exists, find the latest row where PreviousValue is Covered.

We need three pieces of information.

Distinct IDs of things (in your query this is loadid).
For each thing, latest row where CurrentValue is Covered.
For each thing, latest row where PreviousValue is Covered.

We make a sub-select to get answers to each question, left join them together, and pick the first one which is not null.

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
  -- If a thing is not currently covered, cc.id will be null.
  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

Demonstration.

As discussed in the comments, there are design flaws making this query more difficult than it needs to be.

There is no need to store PreviousValue, that is data duplication.
If the initial state is stored, the problem goes away.

The real solution should be to update the table so the initial state is stored with a PreviousState of null. This requires PreviousState to be nullable, and we have to make up a timestamp.

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

Then the PreviousValue can be dropped. Use lag to get the previous value.

select
  *,
  lag(currentvalue) over(
    partition by thingid
    order by datecreated
  ) as PreviousValue
from field_updates
order by thingid, datecreated;

Now your query is much simpler. We just need to find the latest row where CurrentValue is Covered.

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

Demonstration.