TSQL - finding the difference in days of multiple records in SQL Server

Question

Can I find the day difference of different records in SQL Server 2008 R2?

SELECT OrderDate FROM OrdersTbl WHERE SKU='AA0000' ORDER BY ORDERDATE DESC


OrderDate
-----------------------
2009-12-03 00:00:00.000
2009-04-03 00:00:00.000
2008-02-22 00:00:00.000
2008-02-21 00:00:00.000
2007-02-18 00:00:00.000
2007-01-27 00:00:00.000
2006-10-13 00:00:00.000

I would like to know how many days between them are for each order date so that I can find the average frequency. Thanks in advance.

Answer 1

You can do this with a regular table expression and ROW_NUMBER:

WITH OrderDates AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowNumber,
        OrderDate
    FROM OrdersTable 
    WHERE SKU = 'AA0000'
)
SELECT
    AVG(DATEDIFF(DD, O2.OrderDate, O1.OrderDate)) AS AverageFrequency
FROM OrderDates O1
LEFT JOIN OrderDates O2
    ON O2.RowNumber = O1.RowNumber + 1

Answer 2

;With cteDifference as (
    Select SKU, OrderDate, Row_Number() OVER (Partition by SKU Order by OrderDate) as RowNumber
        from OrdersTbl 
)
select cur.SKU, 
       cur.OrderDate as CurrentDate, 
       prev.OrderDate as PreviousDate, 
       DATEDIFF(DD,prev.OrderDate, cur.OrderDate) as DaysDifference 
    from cteDifference cur
        left join cteDifference prev
            on cur.SKU = prev.SKU
                and cur.RowNumber = prev.RowNumber + 1
    where cur.SKU = 'AA0000'
    order by cur.OrderDate desc

Answer 3

SQL Server lacks LEAD / LAG support:

SELECT z.orderdate,
       z.prev_date,
       DATEDIFF(dd, z.prev_date, z.orderdate)
  FROM (SELECT OrderDate,
               (SELECT MAX(y.orderdate)
                  FROM ORDERSTBL y
                 WHERE y.orderdate < x.orderdate
                   AND y.sku = x.sku) AS prev_date
          FROM OrdersTbl x
         WHERE x.sku ='AA0000') z
ORDER BY z.orderdate DESC