query datediff btw different rows in the same table

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

aventisAsked:
Who is Participating?
 
PontisConnect With a Mentor 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! :)
 
0
 
Helena Markováprogrammer-analystCommented:
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
0
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.

All Courses

From novice to tech pro — start learning today.