Identifying gaps between customer orders

Question

I'm stuck. I need to go into a customer order database and identify each month that a customer placed an order after 13 or more months of inactivity. This will be for about 30,000 customers and about 355,000 orders over 7 years.

For example... Say customer # 123 placed an order in the following months:

CustomerNumber  OrderMonth
123             Jan 2010
123             Feb 2010
123             Apr 2010
123             Jul 2011
123             Jan 2013
123             Feb 2013
123             Aug 2015

In this example, I would need three rows returned that contained the following months for customer 123 since each of them had a gap of at least 13 months where there were no orders.

CustomerNumber  OrderMonth
123             Jul 2011
123             Jan 2013
123             Aug 2015

I'm on SQL Server 2008 R2, so Lead/Lag is not available in the tool box. I can normally invent some sort of solution, even if not the most practical/efficient... But this one has me stumped. Any ideas would be appreciated!

what is the datatype of ordermonth? is it a date or a string? — Vamsi Prabhala, Oct 13 '16 at 20:05
Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Oct 13 '16 at 20:06
look at [this](http://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) — techspider, Oct 13 '16 at 20:07

score 2 · Answer 1 · answered Oct 13 '16 at 20:29

Declare @YourTable table (CustomerNumber int, OrderMonth Date)
Insert Into @YourTable values
(123,'2010-01-01'),
(123,'2010-02-01'),
(123,'2010-04-01'),
(123,'2011-07-01'),
(123,'2013-01-01'),
(123,'2013-02-01'),
(123,'2015-08-01')

;with cteBase as (
    Select * 
          ,RowNr = Row_Number() over (Partition By CustomerNumber Order By OrderMonth)
     From @YourTable
)
Select A.CustomerNumber
      ,OrderMonth     = Left(DateName(MM,A.OrderMonth),3)+' '+cast(Year(A.OrderMonth) as varchar(4))
      ,NumberOfMonths = DateDiff(MM,B.OrderMonth,A.OrderMonth)
  From cteBase A
  Join cteBase B on (A.RowNr=B.RowNr+1)
  Where DateDiff(MM,B.OrderMonth,A.OrderMonth)>=13

Returns

CustomerNumber  OrderMonth  NumberOfMonths
123             Jul 2011    15
123             Jan 2013    18
123             Aug 2015    30

Thank you John, I wound up doing pretty much this exact thing based on Zdravko's earlier response. Seems obvious in hindsight, but I was having a complete mental wall. — Jason, Oct 13 '16 at 21:08
@JasonSmith Happens to all of us. Sometimes you just have do some mindless task and it will come to you. Well done. — John Cappelletti, Oct 13 '16 at 21:18

score 1 · Accepted Answer · answered Oct 13 '16 at 20:09

1

Figure out a way how to join the table with itself, but each order is matched to it's previous order. You are not showing all cols there, but imagine you have a field called OrderNumber (it can be calculated). Then you'd write something like that:

SELECT ...
FROM Orders a JOIN Orders b ON a.CutomerId = b.CustomerId AND a.OrderNumber = b.OrderNumber + 1
WHERE DateDiff(a.OrderDate, b.OrderDate, M) > 13

answered Oct 13 '16 at 20:09

Z .

12,657
1
31
56

Haven't tried yet, but this should do the trick.... Thank you for getting me past my mental block Zdravko! – Jason Oct 13 '16 at 20:17

Justin Cave · Answer 3 · 2016-10-13T20:24:21.350

0

You can always go with the generic not exists.

select *
  from your_order_table o
 where not exists( select 1
                     from your_order_table o2
                    where o.CustomerNumber = o2.CustomerNumber
                      and o2.orderDate >= dateadd(month,-13,o.orderDate)
                      and o2.orderDate <  o.orderDate )

edited Oct 13 '16 at 20:24

answered Oct 13 '16 at 20:09

Justin Cave

227,342
24
367
384

there would be many month combinations that satisfy the 13 month gap condition. however, we should look only at the previous row i guess. – Vamsi Prabhala Oct 13 '16 at 20:14
agree vkp... thank you both for your insight and suggestion – Jason Oct 13 '16 at 20:19
@vkp - The `not exists` will cause the query to return any order where there hasn't been another order for that customer in the last 13 months. There is no need to explicitly find the most recent prior order (though the database probably will do so assuming the appropriate index is in place in order to evaluate this). – Justin Cave Oct 13 '16 at 20:23

score 0 · Answer 4 · answered Oct 13 '16 at 20:46

You can use lead to get the difference (for SQL Server > 2012)

;WITH cte
AS (SELECT
    *, LEAD(ordermonth, 1, ordermonth) OVER (PARTITION BY customerNumber ORDER BY ordermonth) AS nextordermonth
FROM yourtable)
SELECT CustomerNumber, nextordermonth
FROM cte
WHERE DATEDIFF(m, ordermonth, nextordermonth) > 13

Table structure

create table yourtable (CustomerNumber Int, OrderMonth date)

Insert Into YourTable values
(123,'2010-01-01'),
(123,'2010-02-01'),
(123,'2010-04-01'),
(123,'2011-07-01'),
(123,'2013-01-01'),
(123,'2013-02-01'),
(123,'2015-08-01')

Would be my first option/approach but OP is on 2008 and Lead/Lag is not an option for him. — John Cappelletti, Oct 13 '16 at 23:12

Identifying gaps between customer orders

4 Answers4