I am trying to obtain the minimum start date for a query, in which the value is equal to its maximum date. So far, I'm able to obtain the value in it's maximum date, but I can't seem to obtain the minimum date where that value remains the same.

Here is what I got so far and the query result:

select a.id, a.end_date, a.value
from database1 as a
inner join (
    select id, max(end_date) as end_date
    from database1
    group by id
) as b on a.id = b.id and a.end_date = b.end_date
where value is not null
order by id, end_date

This result obtains the most recent record, but I'm looking to obtain the most minimum end date record where the value remains the same as the most recent.

In the following sample table, this is the record I'd like to obtain the record from the row where id = 3, as it has the minimum end date in which the value remains the same:

CodePudding user response：

I'm most likely overthinking this as a Gaps & Island problem, but you can do:

select min(end_date) as first_date
from (
  select *, sum(inc) over (order by end_date desc) as grp
  from (
    select *,
      case when value <> lag(value) over (order by end_date desc) then 1 else 0 end as inc
    from t
  ) x
) y
where grp = 0

Result:

first_date
----------
2022-02-14

See running example at SQL Fiddle.

CodePudding user response：

with data as (
    select *,
        row_number() over (partition by value) as rn,
        last_value(value) over (order by end_date) as lv
    from T
)
select * from data
where value = lv and rn = 1

This isn't looking strictly for streaks of consecutive days. Any date that happened to have the same value as on final date would be in contention.

CodePudding user response：

Another option that just approaches the problem somewhat as described for the sample data as shown - Get the value of the maximum date and then the minimum id row that has that value:

select  top(1) t.*
from (
    select top(1) Max(end_date)d, [value] 
    from t 
    group by [value] 
    order by d desc 
)d
join t on t.[value] = d.[value]
order by t.id;

DB<>Fiddle