I have the following scenarion.
I have a table with millions of records and I need to compare rows belonging to the same unitId to increment a counter
What I need to know is detect number that a unit changes from a certain set of color/description to another.
I Filter based on UnitId which is a varchar and a date, that would return thousands of rows from the main table.
Sometimes the CTE runs in les than 10 seconds and someother times it just hangs, that I have to stop the execution of the query even though it is the same query
Any idea what might be causing this hanging or maybe infinite loop somewhere as the sql server process would stick at 50% CPU when I look in task manager
Here is some sample code
WITH CTE AS (
SELECT top ROW_NUMBER() OVER (ORDER BY UnitID, EventDate ) AS RowNum,
UnitID, UnitDescription, EventDate,Color
WHERE EventDate >='2000-01-01T00:00:00'
AND UnitId LIKE 'veh-%'
SELECT COUNT([Current Row].UnitID) AS C,[Current Row].UnitID
FROM CTE [Current Row]
LEFT JOIN CTE [Previous Row]
[Previous Row].RowNum = [Current Row].RowNum - 1
(([Previous Row].Color ='red' AND [Previous Row].UnitDescription='Merce')
( [Previous Row].Color IN ('red','yellow','blue','white')
([Current Row].Color IN ('Orange','Pink') OR [Current Row].UnitDescription='Golf')
([Previous Row].UnitId = [Current Row].UnitId)
GROUP BY [Current Row].UnitID