每组最大日期之间的天数

I want to find out the number of days between each order. I have the date each order was placed and a reference to show if its their first, second third etc order

我想知道每个订单之间的天数。我有每个订单的日期和参考,以显示它的第一个,第二个第三个等订单

What I want to see if how many days there was between order 1 and 2, then how many days between 2 and 3 and so on.

我想看看订单1和2之间有多少天,然后是2到3之间的天数等等。

select OrderID, OrderDate, RankOrder
from Orders

Some example data would be:

一些示例数据将是:

Order 1 -> 01/01/2015 -> 1
Order 2 -> 03/01/2015 -> 2
Order 3 -> 10/01/2015 -> 3

I'd want the results to have an extra column with the number of days:

我希望结果有一个额外的列,其中包含天数:

Order 1 -> 01/01/2015 -> 1 -> 2
Order 2 -> 03/01/2015 -> 2 -> 7
Order 3 -> 10/01/2015 -> 3 -> 0

The last order would probably look at the current date to work out the datediff.

最后一个订单可能会查看当前日期以确定日期。

1 个解决方案

#1

Because you have the ranking function, this is just a join with date arithmetic. Using SQL Server's datediff() function, this looks like:

因为你有排名功能,所以这只是一个日期算术的连接。使用SQL Server的datediff()函数,它看起来像:

select o.*,
       datediff(day, o.OrderDate,
                coalesce(onext.OrderDate, getdate()) as diff
from orders o left join
     orders onext
     on o.rankorder = onext.rankorder - 1;

The date arithmetic depends on the database.

日期算术取决于数据库。

#1