Solved

# Date Difference Consecutive Dates

Posted on 2011-10-10
189 Views
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
0
Question by:Baxters801

LVL 11

Expert Comment

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'))
...
``````
0

Author Comment

I need to check each consecutive row and look 2 years back from that date not from today's date..
0

LVL 11

Expert Comment

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

Author Comment

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

LVL 11

Accepted Solution

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
``````
0

Author Comment

Cool, i think this is gonna work...I will run it and let you know...thank you for all of your time...
0

## Featured Post

### Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…