August 25, 2009 at 12:39 pm
Hi,
I need to compare dates within a dataset to the prior row, but I'm not sure how to do this without writing a loop.
Here's a sample dataset:
CustomerID;OrderID;OrderDate;DaysBetweenOrders
123;12;'2009-04-01';0
123;12;'2009-04-23';22
123;12;'2009-05-14';21
123;43;'2009-05-13';0
123;43;'2009-05-29';16
432;54;'2009-06-01';0
432;54;'2009-06-04';3
432;54;'2009-06-04';0
555;65;'2009-06-22';0
555;67;'2009-06-30';0
653;89;'2009-07-04';0
Basically I need to do a datediff function between each OrderDate and the OrderDate on the prior row, but start back over with a new Order ID and Customer ID. Any suggestions on now to do this without looping through the data?
I'm working on a process that uses Row_Number with an inner select, but I don't know if this'll work. If anyone has suggestions I'd love to hear.
Thanks --
Sam
August 25, 2009 at 12:44 pm
Row_Number() is how I'd do that. It actually does create a loop, but it's a LOT more efficient than a cursor, triangular join, or explicit loop (While loop).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 25, 2009 at 1:02 pm
You can use a cte with row_number to order and get the days. Here's an example using your data
create table cust (CustomerID int, OrderID int,OrderDate smalldatetime,DaysBetweenOrders tinyint)
insert into cust values(123,12,'2009-04-01',0)
insert into cust values(123,12,'2009-04-23',22)
insert into cust values(123,12,'2009-05-14',21)
insert into cust values(123,43,'2009-05-13',0)
insert into cust values(123,43,'2009-05-29',16)
insert into cust values(432,54,'2009-06-01',0)
insert into cust values(432,54,'2009-06-04',3)
insert into cust values(432,54,'2009-06-04',0)
insert into cust values(555,65,'2009-06-22',0)
insert into cust values(555,67,'2009-06-30',0)
insert into cust values(653,89,'2009-07-04',0)
;with cte_cust as (
select CustomerID, OrderID, OrderDate,
ROW_NUMBER() OVER (Partition BY CustomerID, OrderID
ORDER BY CustomerID, OrderID, OrderDate) AS rownum
from cust)
select Curr.CustomerID, Curr.OrderID, Curr.OrderDate,
cast(Isnull(Curr.OrderDate - Next.OrderDate,0) as int) DaysBetweenOrders
from cte_cust Curr
left outer join cte_cust Next
on Curr.CustomerID = Next.CustomerID
and curr.OrderID = Next.OrderID
and Curr.RowNum = Next.RowNum + 1
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 25, 2009 at 1:05 pm
use a common table expression, the row_number() function over customerid and orderid, sort by date asc and self inner join on a.rownum = b.rownum-1
~BOT
Craig Outcalt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply