I have the query now that I am using but I need to go one step further. I need to have only the matching records that where the date difference is 2 years or less...
Hope someone can help..
The query I am using is below...
select DISTINCT * from (
select ID, DATE, VALUE, DID,
row_number() over ( partition by ID order by DATE )AS r1,
count(*) over ( partition by ID ) as r2
where DATE<= CONVERT(DATETIME, '2011-06-30 23:59:59', 102)
AND DATE >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102 )AND
( (HDID = 54) AND (VALUE > '140'))
) a where a.r2 >= 2 and a.r1 <= 2
ORDER BY ID