Trying to find the last time that a value has changed

Question

I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table.

Records are inserted into this table periodically. The ID will always stay the same but occasionally the value will change.

How can I write a query that will give me the ID plus the most recent time the value has changed? Note: the value will always increase.

From this sample data:

  Create Table Taco
 (  Taco_ID int,
    Taco_value int,
    Taco_date datetime)

Insert INTO Taco 
Values (1, 1, '2012-07-01 00:00:01'),
        (1, 1, '2012-07-01 00:00:02'),
        (1, 1, '2012-07-01 00:00:03'),
        (1, 1, '2012-07-01 00:00:04'),
        (1, 2, '2012-07-01 00:00:05'),
        (1, 2, '2012-07-01 00:00:06'),
        (1, 2, '2012-07-01 00:00:07'),
        (1, 2, '2012-07-01 00:00:08')

The result should be:

Taco_ID      Taco_date
1            2012-07-01 00:00:05

(Because 00:05 was the last time Taco_Value changed.)

Answer 1

These two queries rely on the assumption that Taco_value always increases over time.

;WITH x AS
(
  SELECT Taco_ID, Taco_date,
    dr = ROW_NUMBER() OVER (PARTITION BY Taco_ID, Taco_Value ORDER BY Taco_date),
    qr = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date)
  FROM dbo.Taco
), y AS
(
  SELECT Taco_ID, Taco_date,
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID, dr ORDER BY qr DESC)
  FROM x WHERE dr = 1
)
SELECT Taco_ID, Taco_date
FROM y 
WHERE rn = 1;

An alternative with fewer window function madness:

;WITH x AS
(
  SELECT Taco_ID, Taco_value, Taco_date = MIN(Taco_date)
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
), y AS
(
  SELECT Taco_ID, Taco_date, 
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM x
)
SELECT Taco_ID, Taco_date FROM y WHERE rn = 1;

Examples at SQLfiddle

---

Update

For those keeping track, there was contention over what happens if Taco_value could ever repeat. If it could go from 1 to 2 and then back to 1 for any given Taco_ID, the queries will not work. Here is a solution for that case, even if it isn't quite the gaps & islands technique that someone like Itzik Ben-Gan may be able to dream up, and even if it isn't relevant for the OP's scenario - it may be relevant to a future reader. It's a little more complex, and I also added an additional variable - a Taco_ID that only ever has one Taco_value.

If you want to include the first row for any ID where value didn't change at all in the entire set:

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT  
  main.Taco_ID, 
  Taco_date = MIN(CASE 
    WHEN main.Taco_value = rest.Taco_value 
    THEN rest.Taco_date ELSE main.Taco_date 
  END)
FROM x AS main LEFT OUTER JOIN rest
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS 
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
) 
GROUP BY main.Taco_ID;

If you want to exclude those rows, it's a bit more complex, but still minor changes:

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT 
  main.Taco_ID, 
  Taco_date = MIN(
  CASE 
    WHEN main.Taco_value = rest.Taco_value 
    THEN rest.Taco_date ELSE main.Taco_date 
  END)
FROM x AS main INNER JOIN rest -- ***** change this to INNER JOIN *****
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
)
AND EXISTS -- ***** add this EXISTS clause ***** 
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

Updated SQLfiddle examples

Answer 2

Basically, this is @Taryn's suggestion "condensed" to a single SELECT with no derived tables:

SELECT DISTINCT
  Taco_ID,
  Taco_date = MAX(MIN(Taco_date)) OVER (PARTITION BY Taco_ID)
FROM Taco
GROUP BY
  Taco_ID,
  Taco_value
;

Note: this solution takes into account the stipulation that Taco_value can only increase. (More exactly, it assumes that Taco_value cannot change back to a previous value – same as the linked answer, in fact.)

A SQL Fiddle demo for the query: http://sqlfiddle.com/#!3/91368/2

Answer 3

You should be able to use both min() and max() aggregate functions get the result:

select t1.Taco_ID, MAX(t1.taco_date) Taco_Date
from taco t1
inner join
(
    select MIN(taco_date) taco_date,
        Taco_ID, Taco_value
    from Taco
    group by Taco_ID, Taco_value
) t2
    on t1.Taco_ID = t2.Taco_ID
    and t1.Taco_date = t2.taco_date
group by t1.Taco_Id

See SQL Fiddle with Demo

Answer 4

One more answer that is based on the assumption that the values do not reappear (this is basically @Aaron's query 2, condensed in one less nest):

;WITH x AS
(
  SELECT 
    Taco_ID, Taco_value, 
    Rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID
                            ORDER BY MIN(Taco_date) DESC),
    Taco_date = MIN(Taco_date) 
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
)
SELECT Taco_ID, Taco_value, Taco_date
FROM x 
WHERE Rn = 1 ;

Test at: SQL-Fiddle

---

And an answer to the more general problem, where values can reappear:

;WITH x AS
(
  SELECT 
    Taco_ID, Taco_value, 
    Rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID
                            ORDER BY MAX(Taco_date) DESC),    
    Taco_date = MAX(Taco_date) 
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
)
SELECT t.Taco_ID, Taco_date = MIN(t.Taco_date)
FROM x
  JOIN dbo.Taco t
    ON  t.Taco_ID = x.Taco_ID
    AND t.Taco_date > x.Taco_date
WHERE x.Rn = 2 
GROUP BY t.Taco_ID ;

(or using CROSS APPLY so all the related row, including the value, is shown):

;WITH x AS
(
  SELECT 
    Taco_ID, Taco_value, 
    Rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID
                            ORDER BY MAX(Taco_date) DESC),    
    Taco_date = MAX(Taco_date) 
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
)
SELECT t.*
FROM x
  CROSS APPLY 
  ( SELECT TOP (1) *
    FROM dbo.Taco t
    WHERE t.Taco_ID = x.Taco_ID
      AND t.Taco_date > x.Taco_date
    ORDER BY t.Taco_date
  ) t
WHERE x.Rn = 2 ;

Test at: SQL-Fiddle-2

Answer 5

FYI +1 for providing sample structure and data. The only thing I could have asked for is the expected output for that data.

EDIT: This one was going to drive me nuts. I just new there was a "simple" way to do this. I got rid of the incorrect solutions and put one I believe is correct. Here is a solution similar to @bluefeets but it covers the tests that @AaronBertrand gave.

;WITH TacoMin AS (SELECT Taco_ID, Taco_value, MIN(Taco_date) InitialValueDate
                FROM Taco
                GROUP BY Taco_ID, Taco_value)
SELECT Taco_ID, MAX(InitialValueDate)
FROM TacoMin
GROUP BY Taco_ID

Answer 6

Why not just get the difference of the lag value and the lead value? if the difference is zero it didn't change, it it's non zero then it changed. This can be done in a simple query:

-- example gives the times the value changed in the last 24 hrs
SELECT
    LastUpdated, [DiffValue]
FROM (
  SELECT
      LastUpdated,
      a.AboveBurdenProbe1TempC - coalesce(lag(a.AboveBurdenProbe1TempC) over (order by ProcessHistoryId), 0) as [DiffValue]
  FROM BFProcessHistory a
  WHERE LastUpdated > getdate() - 1
) b
WHERE [DiffValue] <> 0
ORDER BY LastUpdated ASC

Answer 7

Had a similar problem today - and in Power BI I could have solved it with the use of Tabler.FillDown. A bit of searching led me to a SQL variant of FillDown:

https://www.oraylis.de/blog/fill-down-table-in-t-sql-last-non-empty-value

So I took the time to adapt that solution to this example - with an extra row added to show reuse of Taco_value.

Insert INTO Taco 
Values (1, 1, '2012-07-01 00:00:09')

Note: this solution takes into account that Taco_value can increase AND decrease (or change back to previous value)

WITH help1
 AS (SELECT 
         *
        ,[ChangeIndicator] = CASE
                                 WHEN [Taco_value] = LAG([Taco_value],1) OVER(
                                      ORDER BY 
         [Taco_ID]) THEN 0
                                 ELSE 1
                             END
        ,[Taco_lag] = LAG([Taco_value],1) OVER(
         ORDER BY 
         [Taco_date])
     FROM [Taco]),
 help2
 AS (SELECT 
         *
        ,[RowGroup] = SUM([ChangeIndicator]) OVER(
         ORDER BY 
         [Taco_date])
     FROM [help1])
 SELECT 
     *
    ,[ChangeDate] = FIRST_VALUE([Taco_date]) OVER(PARTITION BY [RowGroup]
     ORDER BY 
     [Taco_date])
    ,[Taco_FillDown] = FIRST_VALUE([Taco_lag]) OVER(PARTITION BY [RowGroup]
     ORDER BY 
     [Taco_date])
 FROM [help2]
 ORDER BY 
     [Taco_date]

Result:

Answer 8

Could this be as simple as the following?

       SELECT taco_id, MAX(
             CASE 
                 WHEN taco_value <> MAX(taco_value) 
                 THEN taco_date 
                 ELSE null 
             END) AS last_change_date

Given that taco_value always increases?

p.s. I am quite beginner SQL myself, however, learning slowly but surely.