Sql query - how to get when a row first got a certain value

Question

Hi,

I have a table with rows like this:

   ID  StatusId   Date
    1    1        2001-01-01
    2    1        2001-01-02
    3    2        2001-01-03
    4    3        2001-01-04
    5    1        2001-01-05
    6    2        2001-01-06
    7    2        2001-01-07
    8    1        2001-01-08
    9    1        2001-01-09

I need to get the date when the current value of the status was originally changed. For the above example, the last value is 1, and it's changed in row 8, so the result would be 2001-01-08.

How would you do this?

If you need a table to test with, here it is:

DECLARE @Tbl AS TABLE (ID INT, StatusId INT, Date DATETIME)

INSERT INTO @Tbl(ID, StatusId, Date)
SELECT 1,1,'2001-01-01' UNION
SELECT 2,1,'2001-01-02' UNION
SELECT 3,2,'2001-01-03' UNION
SELECT 4,3,'2001-01-04' UNION
SELECT 5,1,'2001-01-05' UNION
SELECT 6,2,'2001-01-06' UNION
SELECT 7,2,'2001-01-07' UNION
SELECT 8,1,'2001-01-08' UNION
SELECT 9,1,'2001-01-09' 

    SELECT * FROM @Tbl

Answer 1

Something like this:

DECLARE @CurrentID INT, @CurrentDate Date

SELECT TOP 1 @CurrentID = ID, @CurrentDate = Date FROM TABLE
ORDER BY Date DESC

SELECT TOP 1 ID, StatusID, Date
FROM Table
WHERE Date < @CurrentDate
AND ID <> @CurrentID
ORDER BY Date DESC

Answer 2

try

select Date
from @Tbl
where StatusId = (
    select StatusId
    from @Tbl
    order by ID desc limit 1)
order by ID desc
limit 1,1

Please check if your database supports limit or not. If not use equivalent of it (e.g. Top).

I have written this as per mysql.

Answer 3

If the table is guaranteed to have one entry per day (as per your sample data), then the following may work

select MAX(t1.Date)
from
   @Tbl t1
      inner join
   @Tbl t2
      on
          t1.Date = DATEADD(day,1,t2.Date) and
          t1.StatusId <> t2.StatusID

Of course, it's possible to further refine this if there are other columns/criteria, of if the value may never have changed at all. Difficult to tell with the small sample size/output example.

Edit 1 If my one entry per day assumption is wrong, then the from clause can be:

from
   @Tbl t1
      inner join
   @Tbl t2
      on
          t1.Date > t2.Date and
          t1.StatusId <> t2.StatusID
      left join
   @Tbl t_successive
      on
          t1.Date > t_successive.Date and
          t2.Date < t_successive.Date
where
   t_successive.ID is null

(Which uses the left join to ensures rows in t1 and t2 don't have any other rows between them)

Answer 4

This one should get you what you're after:

declare @LastStatusID int
declare @LastDate datetime
declare @LastID int

declare @LastChangeID int

/* get last record */
select top 1 @LastStatusID = StatusID, @LastDate = Date, LastID = ID
from @Tbl
order by ID desc

/* get last record with a different status */
select top 1 @LastChangeID = ID
from @Tbl
where ID < @LastID and StatusID <> @LastStatusID
order by ID desc

/* get the first next record - this would get you the last record as well whe it's just been set */
select top 1 Date
from @Tbl
where ID > @LastChangeID
order by ID asc

I haven't included any checking for margin examples when there'd be just one record in the table or multiple of them but all with the same status. You can figure those out yourself.

As a single query

This query requires IDs without gaps and it will get you the last record after a status change and it will also work when there's just one record in the table or multiple of them with the same status (isnull provides the required functionality)

select top 1 Date
from @tbl t1
    left join @tbl t2
    on (t2.ID = t1.ID - 1)
where (isnull(t2.StatusID, -1) <> t1.StatusID)
order by ID desc

Last where clause changes a null value (when there's no upper record) to -1. If you do have a status with this value, you should change this number to some non-existing status value.

Answer 5

This is what I came up with finally:

SELECT T1.ID, T1.StatusId, MIN(T3.Date)
FROM @Tbl T1 INNER JOIN @Tbl T3 ON T1.StatusId = T3.StatusId
WHERE T3.Date > (SELECT MAX(Date) FROM @Tbl T2 WHERE T2.StatusId <> T1.StatusId)
AND T1.ID = (SELECT MAX(ID) FROM @Tbl)
GROUP BY T1.ID, T1.StatusId

and it's doing what I needed it to... thanks everyone