SQL - Filter on dates X number of days apart from the previous

Question

I have a table containing orders. I would like to select those orders that are a certain number of days apart for a specific client. For example, in the table below I would like to select all of the orders for CustomerID = 10 that are at least 30 days apart from the previous instance. With the starting point to be the first occurrence (07/05/2014 in this data).

OrderID | CustomerID |  OrderDate
==========================================
  1           10        07/05/2014
  2           10        07/15/2014
  3           11        07/20/2014
  4           11        08/20/2014
  5           11        09/21/2014
  6           10        09/23/2014
  7           10        10/15/2014
  8           10        10/30/2014

I would want to select OrderIDs (1,6,8) since they are 30 days apart from each other and all from CustomerID = 10. OrderIDs 2 and 7 would not be included as they are within 30 days of the previous order for that customer.

What confuses me is how to set the "checkpoint" to the last valid date. Here is a little "pseudo" SQL.

SELECT OrderID
FROM Orders
WHERE CusomerID = 10 
  AND OrderDate > LastValidOrderDate + 30

Answer 1

You can use the LAG() function, available in SQL Server 2012, together with a Common Table Expression. You calculate the days between the customer's current order and the customer's previous order and then query the Common Table Expression using the filter >= 30

with cte as
(select OrderId
       ,CustomerId
       ,datediff(d
                ,lag(orderdate) over (partition by CustomerId order by OrderDate)
                ,OrderDate) DaysSinceLastOrder
 from Orders)
select OrderId, CustomerId, DaysSinceLastOrder
from cte
where DaysSinceLastOrder >= 30 or DaysSinceLastOrder is null

Results:

OrderId    CustomerId    DaysSinceLastOrder
1          10            NULL
6          10            70
3          11            NULL
4          11            31
5          11            32

(Note that 1970-01-01 is chosen arbitrarily, you may choose any date)

Answer 2

@tinka shows how to use CTEs to do the trick, and the new windowed functions (for 2012 and later) are probably the best answer. There is also the option, assuming you do not have a very large data set, to use a recursive CTE.

Example:

declare @customerid int = 10;
declare @temp table
(orderid int,
customerid int,
orderDate date
);

insert into @temp values  (1,           10,        '07/05/2014')
insert into @temp values  (2,           10,        '07/15/2014')
insert into @temp values  (3,           11,        '07/20/2014')
insert into @temp values  (4,           11,        '08/20/2014')
insert into @temp values  (5,           11,        '09/21/2014')
insert into @temp values  (6,           10,        '09/23/2014')
insert into @temp values  (7,           10,        '10/15/2014')
insert into @temp values  (8,           10,        '10/30/2014');

with datefilter AS
(
    SELECT row_number() OVER(PARTITION BY CustomerId ORDER BY OrderDate) as RowId, 
        OrderId, 
        CustomerId,
        OrderDate, 
        DATEADD(day, 30, OrderDate) as FilterDate
    from @temp
    WHERE CustomerId = @customerid
)
    , firstdate as 
(
    SELECT RowId, OrderId, CustomerId, OrderDate, FilterDate
    FROM datefilter
    WHERE rowId = 1
    union all
    SELECT datefilter.RowId, datefilter.OrderId, datefilter.CustomerId, 
        datefilter.OrderDate, datefilter.FilterDate
    FROM datefilter
    join firstdate
        on datefilter.CustomerId = firstdate.CustomerId 
        and datefilter.OrderDate > firstdate.FilterDate
    WHERE NOT EXISTS 
    (
        SELECT 1 FROM datefilter betweens 
        WHERE betweens.CustomerId = firstdate.CustomerId 
        AND betweens.orderdate > firstdate.FilterDate 
        AND datefilter.orderdate > betweens.orderdate
    )
)

SELECT * FROM firstdate 

Answer 3

Update

A slighty more reliable way of doing it will involve a temporary table. But the original table tbl can be left unchanged. See here:

CREATE TABLE #tmp (id int);   -- set-up temp table
INSERT INTO #tmp VALUES (1);  -- plant "seed": first oid
WHILE (@@ROWCOUNT>0)
  INSERT INTO #tmp (id)
  SELECT TOP 1 OrderId FROM tbl 
  WHERE OrderId>0 AND CustomerId=10 
        AND OrderDate>(SELECT max(OrderDate)+30 FROM tbl INNER JOIN #tmp ON id=OrderId)
  ORDER BY OrderDate;

-- now list all found entries of tbl:
SELECT * FROM tbl WHERE EXISTS (SELECT 1 FROM #tmp WHERE id=OrderId)

Answer 4

i came here and i saw @SveinFidjestøl already posted answer but i can't control my self after by long tried : with the help of LAG and LEAD we can comparison between same column and as per your Q you are looking 1,6,8. might be this is helpful

SQL SERVER 2012 and after

declare @temp table
(orderid int,
customerid int,
orderDate date
);

insert into @temp values  (1,           10,        '07/05/2014')
insert into @temp values  (2,           10,        '07/15/2014')
insert into @temp values  (3,           11,        '07/20/2014')
insert into @temp values  (4,           11,        '08/20/2014')
insert into @temp values  (5,           11,        '09/21/2014')
insert into @temp values  (6,           10,        '09/23/2014')
insert into @temp values  (7,           10,        '10/15/2014')
insert into @temp values  (8,           10,        '10/30/2014');

with cte as
(SELECT orderid,customerid,orderDate,
LAG(orderDate) OVER (ORDER BY orderid ) PreviousValue,
LEAD(orderDate) OVER (ORDER BY orderid) NextValue,
rownum = ROW_NUMBER() OVER (ORDER BY orderid) 
FROM @temp
WHERE customerid = 10) 

select orderid,customerid,orderDate from cte
where DATEDIFF ( day , PreviousValue  ,  orderDate) > 30 
or PreviousValue is null or NextValue is null

SQL SERVER 2005 and after

WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.orderid),
p.orderid,
p.customerid,
p.orderDate
FROM @temp p
where p.customerid = 10)

SELECT CTE.orderid,CTE.customerid,CTE.orderDate,
prev.orderDate PreviousValue,
nex.orderDate NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
where CTE.customerid = 10
 and
DATEDIFF ( day , prev.orderDate  ,  CTE.orderDate) > 30 
or prev.orderDate is null or nex.orderDate is null
GO