I have above table where the item is changing from A to B and then B to A (etc). The the most recent item in the table based on the date is A (the last row).

I need to find the date on which this last item (A) was started to be in effect.

So in this case the item A was in effect from 2022-01-09 onwards (UniqueId 9).

How can I find the UniqueId or the date of item A, where it got changed to be in effect (Row 9)?

Thank you.

CodePudding user response：

with data as (
    select *,
        last_value(item) over (order by "date") as last_item,
        lag(item) over (order by "date") as prev_item
    from T
)
select
    max(case when item = last_item and item <> prev_item then "date" end) as max_date
from data;

or

with data as (
    select *,
        case when item <> lag(item)        over (order by "date")
              and item =  last_value(item) over (order by "date")
             then 1 end as flag
    from T
)
select max("date") as last_transition_date
from data
where flag = 1;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=bd5f6398c0167d74c26a67fafac5225e

Supposing you need all the data:

with data as (
    select *,
        case when item <> lag(item)        over (order by "date")
              and item =  last_value(item) over (order by "date")
             then 1 end as flag
    from T
)
select *,
    max(case when flag = 1 then "date" end) over () as last_transition_date
from data;

CodePudding user response：

Getting a flag using a comparison of current item with previous item in time, using LAG() is indeed the way.

But it's absolutely sufficient to get the highest date and highest unique (as both are sorted ascending together) where the obtained flag is 1:

WITH
-- your input
indata(UniqueId,ITEM,DATE) AS (
          SELECT 1,'A',DATE '2022-01-01'
UNION ALL SELECT 2,'A',DATE '2022-01-02'
UNION ALL SELECT 3,'B',DATE '2022-01-03'
UNION ALL SELECT 4,'B',DATE '2022-01-04'
UNION ALL SELECT 5,'A',DATE '2022-01-05'
UNION ALL SELECT 6,'A',DATE '2022-01-06'
UNION ALL SELECT 7,'B',DATE '2022-01-07'
UNION ALL SELECT 8,'B',DATE '2022-01-08'
UNION ALL SELECT 9,'A',DATE '2022-01-09'
UNION ALL SELECT 10,'A',DATE '2022-01-10'
UNION ALL SELECT 11,'A',DATE '2022-01-11'
)
-- real query starts here; replace following comma with "WITH"
,
w_change_ind AS (
  SELECT
    *
  , CASE WHEN LAG(item) OVER(ORDER BY date) <> item
      THEN 1
      ELSE 0
    END AS chg_ind
  FROM indata
)
SELECT
  MAX(uniqueid) AS uqid
, MAX(date)     AS dt
FROM w_change_ind
 WHERE chg_ind=1
;
-- out  uqid |     dt     
-- out ------ ------------
-- out     9 | 2022-01-09                                                                                                                                                         

CodePudding user response：

Based on your description, this is one way to do what you want.

select top 1 * from table1
where item ='A'
order by uniqueid desc

If this is not what you want, then you will have to provide additional information.