行之间的SQL日期差异 [英] SQL date difference between rows

问题描述

您好我正在尝试获取sql中两行之间的日期差异。我希望获得EventTypeDec'Match'和'Accommodate'之间的天数，其中EventType在11和15中。

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.

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

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

推荐答案

您的核心问题是每对都没有唯一的ID。如果他们这样做，这将更容易。事实上，您需要按日期排序。

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.

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

将在SS2012中运行。您可以添加case语句，以便不返回匹配行的值。如果你有一个理智的数据库结构然后它可以很容易地用CTE，SS2008或2005完成它将更加简洁。它仍然可以在2005年或2008年完成但我不想再做任何事情，直到我知道哪个你正在使用的，以及为什么你的数据库结构坏了。



这里是SS 2008

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

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

由于您不能在不同的行之间使用此类函数，因此必须进行内连接在 EventType 字段中，然后将该函数应用于连接结果。

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.

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

这篇关于行之间的SQL日期差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！