Joining on the same key on the next row

Suppose we have a table which contains customer_id, order_date, and ship_date. A reorder of the product occurs when the same customer’s next order_date is within 30 days of the last ship_date.

select * from mytable

customer_id       order_date      ship_date
   1              2017-08-04      2017-08-09
   1              2017-09-01      2017-09-05
   2              2017-02-02      2017-03-01
   2              2017-04-05      2017-04-09
   2              2017-04-15      2017-04-19       
   3              2018-02-02      2018-03-01

Requested: Reorders

customer_id       order_date      ship_date
   1              2017-09-01      2017-09-05
   2              2017-04-15      2017-04-19

How can I retrieve only the records for the same customers who had reorders, next order_date within 30 days of the last ship_date.

Answer

You can use exists as follows:

Select * from your_table t
Where exists (select 1 from your_table tt
Where tt.customer_id = t.customer_id
  And t.ship_date > tt.ship_date
  and t.ship_date <= dateadd(day, 30, tt.ship_date))

Advertisement

Answer