Difference between dates in different rows...

Hi all,

I have a table named Orders and this table has two relevant fields: CustomerId and OrderDate. I am trying to construct a query that will give me the difference, in days, between each customer's order so that the results would be something like: (using Northwind as the example)

...
ALFKI 25/08/1997 03/10/1997 39
ALFKI 03/10/1997 13/10/1997 10
ALFKI 13/10/1997 15/01/1998 94
ALFKI 15/01/1998 16/03/1998 60
ALFKI 16/03/1998 09/04/1998 24
...

At the moment, I have the following query that I think is on the right track:
…
SELECT dbo.Orders.CustomerID, dbo.Orders.OrderDate AS LowDate, Orders_1.OrderDate AS HighDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) AS Difference FROM dbo.Orders INNER JOIN dbo.Orders Orders_1 ON dbo.Orders.CustomerID = Orders_1.CustomerID AND dbo.Orders.OrderDate < Orders_1.OrderDate GROUP BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) ORDER BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate
…

However, this gives me too much data:
…
ALFKI 25/08/1997 03/10/1997 39
ALFKI 25/08/1997 13/10/1997 49
ALFKI 25/08/1997 15/01/1998 143
ALFKI 25/08/1997 16/03/1998 203
ALFKI 25/08/1997 09/04/1998 227
ALFKI 03/10/1997 13/10/1997 10
ALFKI 03/10/1997 15/01/1998 104
ALFKI 03/10/1997 16/03/1998 164
ALFKI 03/10/1997 09/04/1998 188
ALFKI 13/10/1997 15/01/1998 94
ALFKI 13/10/1997 16/03/1998 154
ALFKI 13/10/1997 09/04/1998 178
ALFKI 15/01/1998 16/03/1998 60
ALFKI 15/01/1998 09/04/1998 84
…

So, do any of you have any ideas how I might achieve this? I know how to do it using a stored procedure, but I am trying to avoid that; I’d like to do this in a single query.

Thanks for any help you have to offer,

Regards,

Stephen.

SQL Server 2005:

SELECT a.CustomerID, a.OrderDate as Highdate, b.OrderDate as LowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs

FROM (SELECT CustomerID, OrderDate, ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) as RowNum FROM dbo.Orders) a

INNER JOIN (SELECT CustomerID, OrderDate, (ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) -1)as RowNumMinusOne

FROM dbo.Orders) b ON a.CustomerID=b.CustomerId AND a.RowNum=b.RownumMinusOne

|||

SQL Server 2000:

SELECT a.CustomerID, a.OrderDate as HighDate, b.OrderDate as lowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs FROM (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T.CustomerID and OrderDate < T.OrderDate ) + 1 as Rank1

from Orders as T ) a INNER JOIN (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T1.CustomerID and OrderDate < T1.OrderDate ) as Rank2

from Orders as T1 ) b ON b.CustomerID=a.CustomerID and a.Rank1=b.Rank2

ORDER BY a.CustomerID, a.OrderDate

|||

You're an absolute star! Just what I was after. My head was starting to spin trying to figure this one out.

Thank you for your help!

Regards,

Stephen.

sql