I have a snippet of SQL that compared the last two records and gives the `datediff` in seconds, however the way I have it is quite slow taking up to 20 seconds to execute depending how many controllerID's I needs to check.

What would be a more efficient way of do doing this?

``selectT.controllerID,datediff(ss, T.Max_dtReading, T1.Max_dtReading) As ElaspedTimefrom(selectcontrollerID,max(dtReading) as Max_dtReadingfromReaderDatawhereCardID = 'FFFFFFF0' AND (controllerID in(2,13,28,30,37,40))group bycontrollerID) as Touter apply(selectmax(T1.dtReading) as Max_dtReadingfromReaderData as T1whereT1.CardID = 'FFFFFFF0' AND (controllerID in(2,13,28,30,37,40))and T1.controllerID = T.controllerIDand T1.dtReading < T.Max_dtReading) as T1``

I might suggest conditional aggregation for this:

``````select controllerID,
) As ElaspedTime
row_number() over (partition by controllerID order by dtReading desc) as seqnum
where CardID = 'FFFFFFF0' AND
controllerID in (2, 13, 28, 30, 37, 40)
) r
where seqnum <= 2
group by controllerID
``````

You can use `ROW_NUMBER()` in order to locate the records with the 2 highest `dtReading` values, then join these together to calculate the difference:

``````;WITH CTE AS (
ROW_NUMBER() OVER (PARTITION BY controllerID
ORDER BY dtReading DESC) AS rn
WHERE CardID = 'FFFFFFF0' AND (controllerID IN (2,13,28,30,37,40))
)
SELECT c1.controllerID,
FROM CTE c1
INNER JOIN CTE c2 ON (c1.controllerID = c2.controllerID)
AND c1.rn = 1 AND c2.rn = 2
``````
``````;WITH CTE AS
(select controllerID
,ROW_NUMBER() OVER (PARTITION BY controllerID ORDER BY dtReading DESC) rn