One way to do this is as follows:SELECT
a.StoreId,
a.CustomerId,
DATEDIFF( dd,b.DateofPurchase, a.DateofPurchase) AS Interval
FROM
YourTable a
CROSS APPLY
(
SELECT TOP (1) b.DateOfPurchase
FROM YourTable b
WHERE b.DateofPurchase < a.DateofPurchase
AND a.CustomerId = b.CustomerId
ORDER BY b.DateofPurchase DESC
) b
WHERE
DATEDIFF( dd,b.DateofPurchase, a.DateofPurchase) < 10
select customer purchase less than 10 days apart
Posted - 07/06/2012 : 07:34:33
