CODEWITHSUNDEEP
sqlt-sqlsql-server-2008averagedatediff
pqsk
pqsk

Reputation: 2174

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

Is it possible to find the difference of days 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 a way to get how many days in between there are for each order date so that I could find the average frequency. Thanks in advance.

Upvotes: 3

Views: 4887

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

;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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332531

Sucks there's no LEAD/LAG support in SQL Server:

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

Upvotes: 1

Bennor McCarthy
Bennor McCarthy

Reputation: 11675

You can do it with a common 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

Upvotes: 6

Related Questions