Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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
0
Baxters801
Asked:
Baxters801
  • 3
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now