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
I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this.
The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee
on the entire table. So as always, test with your own situation. This query also assumes that the MAX
date is either unique or that equivalent rows would be acceptable.
USE AdventureWorks2012
GO
;
WITH Base AS (
SELECT
TransactionHistory.*
,ProductVendor.BusinessEntityID
,MAX(CASE WHEN TransactionDate < '2008-08-01' THEN TransactionDate END)
OVER (PARTITION BY ProductVendor.BusinessEntityID) AS PreviousVendorTransaction
,COUNT(CASE WHEN TransactionDate >= '2008-08-01' THEN 1 END )
OVER (PARTITION BY ProductVendor.BusinessEntityID) AS VendorAfterCutoff
FROM
Production.TransactionHistory
-- Doesn't make the most sense, but I need a repeating relation
INNER JOIN Purchasing.ProductVendor
ON TransactionHistory.ProductID = ProductVendor.ProductID
),
Filtered AS (
SELECT
*
FROM
Base
WHERE
Base.TransactionDate >= '2008-08-01'
OR (TransactionDate = PreviousVendorTransaction AND VendorAfterCutoff > 0)
)
SELECT DISTINCT
TransactionID
,ProductID
,ReferenceOrderID
,ReferenceOrderLineID
,TransactionDate
,TransactionType
,Quantity
,ActualCost
,ModifiedDate
FROM
Filtered
Edit:
Hmm, I think I may have to take back my comment on structuring it differently if there are indexes. The other suggestions that I have are probably fairly minor.
- Make sure the query is using the indexes you're expecting it to. Start and End date to build temp table, end date to drive the previous event loop.
- If the query to build the temp table is doing a lookup on the clustered index, it may be better to hold off and do that as part of the main query.
- Try using a cte instead of a temp table. I think that a cte might be more competitive with the way that the query is structured below.
- If you are returning a lot of events, it might be better to pull out the event table lookup to the main query to give the optimizer the option of doing a merge join.
- I don't see a way of optimizing the previous event lookup short of an indexed view.
Here's a query that combines a few of those ideas.
SELECT
e.[EventID]
INTO #EventTemp
FROM
[Events] AS e
WHERE
( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )
;
WITH PrevEvent AS (
SELECT
EmpPrevEvent.[EventID]
FROM
(
SELECT DISTINCT
ee.[EmployeeID]
FROM
#EventTemp
INNER JOIN [EventEmployee] AS ee ON
#EventTemp.[EventID] = ee.[EventID]
) AS Emp
CROSS APPLY (
SELECT TOP 1
e.[EventID]
FROM
[Events] AS e
INNER JOIN [EventEmployee] AS ee ON
e.[EventID] = ee.[EventID]
WHERE
ee.[EmployeeID] = Emp.[EmployeeID]
AND e.[EventEnd] < @StartDate
ORDER BY
e.[EventEnd] DESC
) AS EmpPrevEvent
)
SELECT
e.[EventID],
e.[EventStart],
e.[EventEnd],
e.[EventTypeID]
FROM
[Events] AS e
WHERE
e.EventID IN (
SELECT EventID
FROM #EventTemp
UNION
SELECT EventID
FROM PrevEvent
)
Best Answer
I don't know the logic for columns
HIGH
,LOW
...the desired output: