The most compact syntax I can find is:
SELECT *
FROM @T AS t
WHERE EXISTS
(
SELECT ISNULL(ISNULL(a, b), c)
INTERSECT
SELECT ISNULL(ISNULL(b, c), a)
INTERSECT
SELECT ISNULL(ISNULL(c, a), b)
);
Based on an idea from one of my old blog posts that describes how to use INTERSECT and EXCEPT to replace comparisons like a <> b OR (a IS NULL AND b IS NULL) with NOT EXISTS (a INTERSECT b).
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
Best Answer
I believe the easiest way would be using LAG or LEAD functions. They allow you to accesses data from a previous or subsequent row in the same result set without the use of a self-join. But they're available starting with SQL Server 2012 (11.x).
So your solution would require the a self-join like this: