当我们只有一个日期列时，SQL Server 2008会计算数据差异

I have a date column Order_date and I am looking for ways to calculate the date difference between customer last order date and his recent previous ( previous form last) order_date ....

我有一个日期列Order_date,我正在寻找方法来计算客户最后订单日期和他最近的上一个(前一个表格最后)order_date之间的日期差异....

Example

Customer :      1,    2 ,   1 ,   1  
Order_date:   01/02/2007,  02/01/2015, 06/02/2014, 04/02/2015

As you can see customer # 1 has three orders.

正如您所见,客户#1有三个订单。

I want to know the date difference between his recent order date (04/02/2015) and his recent previous (06/02/2014).

我想知道他最近的订单日期(04/02/2015)和他最近的订单日期(06/02/2014)之间的日期差异。

Thanks

5 个解决方案

#1

For SQL Server 2012 & 2014 you could use LAG with a DATEDIFF to see the number of days between them.

对于SQL Server 2012和2014,您可以使用带有DATEDIFF的LAG来查看它们之间的天数。

For older versions, a CTE would probably be your best bet:

对于旧版本,CTE可能是您最好的选择:

;WITH CTE AS 
(
 SELECT CustomerID,
        Order_Date,
        rn = ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Order_Date DESC)
)
SELECT c1.CustomerID,
       DATEDIFF(d, c1.Order_Date, c2.Order_Date)
FROM CTE c1
INNER JOIN CTE c2 ON c2.rn = c1.rn + 1

#2

In SQL Server 2012+, you can use lag() to get the difference between any two dates:

在SQL Server 2012+中,您可以使用lag()来获取任意两个日期之间的差异:

select t.*,
       datediff(day, lag(order_date) over (partition by customer order by order_date),
                order_date) as days_dff
from table t;

If you have an older version, you can do something similar with correlated subqueries or outer apply.

如果您有旧版本,则可以使用相关子查询或外部应用执行类似操作。

EDIT:

If you just want the difference between the two most recent dates, use conditional aggregation instead:

如果您只想要两个最近日期之间的差异,请使用条件聚合:

select customer,
       datediff(day, max(case when seqnum = 2 then order_date end),
                 max(case when seqnum = 1 then order_date end)
               ) as MostRecentDiff
from (select t.*,
             row_number() over (partition by customer order by order_date desc) as seqnum
      from table t
     ) t
group by customer;

#3

If you're using SQL Server 2008 or later, you can try CROSS APPLY.

如果您使用的是SQL Server 2008或更高版本,则可以尝试CROSS APPLY。

SELECT [customers].[customer_id], DATEDIFF(DAY, MIN([recent_orders].[order_date]), MAX([recent_orders].[order_date])) AS [elapsed]
FROM [customers]
CROSS APPLY (
  SELECT TOP 2 [order_date]
  FROM [orders]
  WHERE ([orders].[customer_id] = [customers].[customer_id])
) [recent_orders]
GROUP BY [customers].[customer_id]

#4

SELECT DATEDIFF(DAY, Y.PrevLastOrderDate, Y.LastOrderDate) AS PreviousDays
FROM
(
    SELECT X.LastOrderDate
        , (SELECT MAX(OrderDate) FROM dbo.Orders SO WHERE SO.CustomerID=1 AND SO.OrderDate < X.LastOrderDate) AS PrevLastOrderDate
    FROM
    (
        select MAX(OrderDate) AS LastOrderDate
        FROM dbo.Orders O 
        WHERE O.CustomerID=1
    )X
)Y

#5

drop table #Invoices


create table #Invoices ( OrderId int , OrderDate datetime )

insert into #Invoices (OrderId , OrderDate ) 
select 101, '01/01/2001' UNION ALL Select 202, '02/02/2002' UNION ALL Select 303, '03/03/2003'
 UNION ALL Select 808, '08/08/2008' UNION ALL Select 909, '09/09/2009'

;
WITH
MyCTE /* http://technet.microsoft.com/en-us/library/ms175972.aspx */
( OrderId,OrderDate,ROWID) AS
(
SELECT
OrderId,OrderDate
, ROW_NUMBER() OVER ( ORDER BY OrderDate ) as ROWID
FROM
#Invoices inv
)

SELECT
OrderId,OrderDate

,(Select Max(OrderDate) from MyCTE innerAlias where innerAlias.ROWID = (outerAlias.ROWID-1) ) as PreviousOrderDate
,
[MyDiff] =
CASE 
 WHEN (Select Max(OrderDate) from MyCTE innerAlias where innerAlias.ROWID = (outerAlias.ROWID-1) ) iS NULL then 0
 ELSE DATEDIFF (mm, OrderDate , (Select Max(OrderDate) from MyCTE innerAlias where innerAlias.ROWID = (outerAlias.ROWID-1) ) )
END

, ROWIDMINUSONE = (ROWID-1)
, ROWID as ROWID_SHOWN_FOR_KICKS , OrderDate as OrderDateASecondTimeForConvenience
FROM
MyCTE outerAlias


ORDER BY outerAlias.OrderDate Desc , OrderId

#1