SQL date difference between rows

Question

0.00/5 (No votes)

See more:

Hi I am trying to get the date difference between two rows in sql. I want to get number of days between EventTypeDec 'Match' and 'Accommodate' where EventType is in 11 and 15.

CSS

ID  EventTypeID EventType   ScheduledCompletion EventTypeDes
2   39           11         04/12/2012   18:58     Match
2   267          15         07/12/2012   21:00     Accommodate
2   383          11         10/12/2012   18:50     Match
2   3103         15         06/02/2013   16:30     Accommodate

Desired output

CSS

ID  EventTypeID EventType   ScheduledCompletion EventTypeDes    NumDays
2   39              11         04/12/2012 18:58    Match
2   267             15         07/12/2012 21:00    Accommodate       3
2   383             11         10/12/2012 18:50    Match
2   3103            15         06/02/2013 16:30    Accommodate      50(roughly)

I know how to use date difference function between two columns but no idea how to use it in two rows.
Would appreciate any help. Thanks in advance

Posted 20-Jan-14 2:48am

chan200uk

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2014-01-20T10:59:00

Your core issue is that each pair does not have a unique id. If they did, this would be a lot easier. As it is, you need to just order by dates.

SQL

select id, eventtypeid, eventtype, ScheduledCompletion, EventTypeDes,
datediff(day,
lag(ScheduledCompletion, 1, getdate()) over (order by ScheduledCompletion), ScheduledCompletion) as Gap
from tblTest

will work in SS2012. You can add a case statement to not return a value for the 'match' rows. It would be much neater if you had a sane DB structure then it could easily be done with CTEs, and for SS2008 or 2005. It could still be done for 2005 or 2008 but I don't want to do any more until I know which one you're using, and why your DB structure is broken.

Here it is for SS 2008

SQL

with tbl as
(
 select row_number() over (order by scheduledcompletion) as r, id, eventtypeid, eventtype, scheduledcompletion, eventtypedes from tbltest
)

select r1.id, r1.eventtypeid, r1.eventtype, r1.scheduledcompletion, r1.eventtypedes,
case when r1.eventtype = 11 then '' else
convert(varchar(5),
datediff(day,
r2.ScheduledCompletion, r1.ScheduledCompletion)) end as Gap
from tbl r1 left join tbl r2 on r1.r = r2.r+1

CPallini · Answer 2 · 2014-01-20T03:01:00

Solution 1

Since you cannot use such function between different rows you have to make a inner join on the EventType field and then apply the function to the join result.

Posted 20-Jan-14 3:01am

CPallini

Comments

chan200uk 20-Jan-14 9:05am

Thanks for the reply, any example please

Ahmed Bensaid · Answer 3 · 2014-01-20T03:02:00

Solution 2

http://stackoverflow.com/questions/2357515/calculate-time-difference-between-two-rows-sql-server[^]

Posted 20-Jan-14 3:02am

Ahmed Bensaid