query datediff btw different rows in the same table

aventis
aventis used Ask the Experts™
on
Hello,

I have a table history like this

task    date                     id
start    Oct 1, 2003         abc
end     Oct 3, 2003         abc

I would like find out any id which datediff(day, end, start) >=2,

how can I do this?

Thanks in advance,

Aventis

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:


 select distinct id from (select id, date - lag(date)   over (partition by id order by task desc)  diff from table) where diff>=2
/

Analytical functions rule! :)
 
Helena Markováprogrammer-analyst

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept Pontis's comment as answer.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial