Jason Smith Jason Smith - 1 month ago 8
SQL Question

Identifying gaps between customer orders

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!

Answer

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
Comments