Query that filters based on consecutive dates

• Jonathan Cohen

  SSC Enthusiast

  Points: 116

  More actions

  November 22, 2004 at 7:11 pm

  #84782

  Hi,

  I am trying to build a query which would filter based on the DATEDIFF between a date field in one record and the same date field in the previous record within an appropriate Grouping and Sort.

  I am sure that there is a neat way for doing it, but I just don't know how.

   

   

  Thanks,   J

   

   

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  November 22, 2004 at 7:21 pm

  #531156

  Can you supply a bit more detail?

• Jonathan Cohen

  SSC Enthusiast

  Points: 116

  More actions

  November 22, 2004 at 7:47 pm

  #531157

  Let us say that we have a table with 3 fields fields: EventLog EventDescription and EventDate.

   

  I would like to find all the events which the previous event was more than 10 days before:

   

  EventDate(Record x) - EventDate(Record x-1) > 10

• wz700

  SSCertifiable

  Points: 7133

  More actions

  November 22, 2004 at 10:57 pm

  #531166

  try this

  select * from eventtable a

   where exists(select * from eventtable b

   where a.eventlog = b.eventlog

   and datediff(dd, b.eventdate, a.eventdate) > 10)

  -- and a.eventdate >= ''