Date Difference Consecutive Dates

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

            from OBS

            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
Baxters801Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brutaldevCommented:
Use the DATEDIFF function:
...
where  DATE<= CONVERT(DATETIME, '2011-06-30 23:59:59', 102)
AND DATE >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102 )

-- Change your accuracy here (year, month, day etc.) depending on how close you want the dates to be, not sure what you want to compare against so just using GETDATE()
AND DATEDIFF(month, DATE, GETDATE()) <= 24  -- Less than or equal to 24 months

AND ( (HDID = 54) AND (VALUE > '140'))
...

Open in new window

0
Baxters801Author Commented:
I need to check each consecutive row and look 2 years back from that date not from today's date..
0
brutaldevCommented:
Use a combination of DATEDIFF and DATEADD (subtracting 2 years) to determine the difference. I still don't see the two dates you want to compare otherwise I would give you a code sample.
...                
) a where a.r2 >= 2 and a.r1 <= 2
-- Use DATEDIFF and DATEADD here to check the difference of all dates coming out of the query.
ORDER BY ID
...
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Baxters801Author Commented:
I am trying to compare the two dates from each two consecutive records

ID,  DATE,  VALUE,  DID,

1   1/10/2007  54     31
2  1/11/2006  54     27
2  3/15/2007  54      36
3  4/18/2006   54    36
3  4/18/2007  54     33

So for every two consecutive records that are of the same id I need to check if the dates are 2 years or less....For example   for ID 2 above the date difference would be 1 year..For ID 3 the date difference would be 1 year...and so on...




0
brutaldevCommented:
This isn't easy to achieve in a single statement without a PIVOT. How exactly do you want this displayed, as an additional column? If you just want to filter out the records, you could post-process them records into another (temporary) table and select off it afterwards.

Here is a full code example to filter out the records less than two years apart, I tried to keep it as simple as possible using SQL you are already familiar with:
-- Select records into a temp table to process.
select DISTINCT * 
into #MyTempTable  /* Store results in a temp table */
from (
  select ID, DATE, VALUE, DID,
  row_number() over ( partition by  ID order by DATE )AS r1,
  count(*) over ( partition by  ID ) as r2
  from OBS
  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

declare @currentId int
declare @lastId int
select @currentId = 0, @lastId = max(ID)
from #MyTempTable

-- Process the records by removing ones that are less than two years apart.
while (@currentId <= @lastId)
begin
  -- Get the next ID to process
  select top 1 @currentId = ID
  from #MyTempTable
  where ID > @currentId
  order by ID
  
  -- Get the dates to compare
  declare @date1 datetime, @date2 datetime
  
  select @date1 = DATE
  from (
    select *, row_number() over ( partition by ID order by DATE) AS r1
    from #MyTempTable
    where ID = @currentId
  ) a where a.r1 = 1
  order by DATE
  
  select @date2 = DATE
  from (
    select *, row_number() over ( partition by ID order by DATE) AS r1
    from #MyTempTable
    where ID = @currentId
  ) a where a.r1 = 2
  order by DATE
  
  declare @diff int
  set @diff = datediff(year, @date1, @date2)
  
  -- Remove records that are not within 2 years, you could easily update a colum as well on the temp table with the year difference
  if (isnull(datediff(year, @date1, @date2), 0) < 2) delete from #MyTempTable where ID = @currentId
  
  -- Get out of the loop if we are on the last record
  if (@currentId = @lastId) set @currentId = @lastId + 1
end

-- Get all the records that remain in the temp table
select * from #MyTempTable

-- Delete the temp table
drop table #MyTempTable

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Baxters801Author Commented:
Cool, i think this is gonna work...I will run it and let you know...thank you for all of your time...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.