|
I can only comment based on a SQL Server background but you should be able to do it if you can a) Create a temp table or memory table (easiest) or b) use a cursor. This is necessary to create the sub selects and their respective ID's. Then you can use something like this to link your sub queries together (note: @dates is either a temporary table built using your data table and either ):
SELECT TOP 1
d1,
d2,
d1 - d2 [difference]
FROM (SELECT [ID = AUTONUMBER],Date1 [d1] FROM @dates WHERE Date1 NOT IN (SELECT MIN(Date1) FROM @dates)) t1
INNER JOIN (SELECT [ID = AUTONUMBER],Date1 [d2] FROM @dates WHERE Date1 NOT IN (SELECT MAX(Date1) FROM @dates)) t2
ON t1.ID = t2.ID
WHERE d1 - d2 > 30
ORDER BY d1 DESC
So where I wrote [ID = AUTONUMBER] this is an ID that is generated via cursor or as an auto-generated field if you use a temp table. I used this just to illustrate the basic structure of the query not knowing what your capabilities are with your software platform.
__________________
Happiness is just a roughing penalty away.
|