Query to get the customers who haven't transacted in the past two months

Question

I have some data in the following format (table name - ORDERS):

╔══════════╦═════════╦═════════════════════════════╗
║ OrderID  ║ CustNum ║ OrderDate                   ║
╟──────────╫─────────╢─────────────────────────────╢
║ 1        ║ 100     ║ 2015-02-05 00:00:00.0000000 ║
║ 2        ║ 101     ║ 2015-03-05 00:00:00.0000000 ║
║ 4        ║ 102     ║ 2015-04-05 00:00:00.0000000 ║
║ 5        ║ 102     ║ 2015-05-05 00:00:00.0000000 ║
║ 6        ║ 102     ║ 2015-06-05 00:00:00.0000000 ║
║ 10       ║ 101     ║ 2015-06-05 00:00:00.0000000 ║
║ 34       ║ 100     ║ 2015-06-05 00:00:00.0000000 ║
╚══════════╩═════════╩═════════════════════════════╝

and I have a customers table having customer information in the following format:

╔═════════╦══════════╗
║ CustNum ║ CustName ║
╟─────────╫──────────╢
║ 100     ║ ABC      ║
║ 101     ║ DEF      ║
║ 102     ║ GHI      ║
╚═════════╩══════════╝

This data spans many years, the ORDERS table itself has 5 Million+ records. I would like to know the number of customers who haven't transacted (or placed an order) in the past two months.

In the above example, CustNum 100 and 101 haven't transacted for the past two months (if we look at June 2015) and 102 had transacted in the past two months (again, if we look at June 2015), so I would like the output to be in the following format:

Time        NumberOfCustNotPlacingOrders
Apr-2014    1 (CustNum 102, didnt place orders in Feb and Mar)
May-2014    1 (CustNum 100, didnt place orders in Mar and Apr)
Jun-2014    2 (CustNum 100 and 101, didnt place orders in Apr and Mar)

In other words, I would like to look at an entire month, let's say June 2015 in this case. I would now look to peek 2 months back (April and May) and count how many customers (CustNum) didn't place orders, 100 and 101 in this case. I would like to do this for all the months, say starting Jan 2012 till Jun 2015.

I would post the initial query that I would have tried, but I really don't have any idea how to achieve this, so it's pretty much a blank slate for me. Seems like a self join maybe in order, but I'm not entirely sure.

Any help would be highly appreciated.

Answer 1

Here is the first solution, which could be used as a working base.

CREATE TABLE #orders(OrderId int identity(1,1), CustNum int, Orderdate date)

-- using system columns to populate demo data (I'm lazy)
INSERT INTO #orders(CustNum,Orderdate)
    SELECT system_type_id, DATEADD(month,column_id*-1,GETDATE())
    FROM sys.all_columns

-- Possible Solution 1:
-- Getting all your customers who haven't placed an order in the last 2 months
SELECT *
FROM (
        -- All your customers
        SELECT DISTINCT CustNum
        FROM #orders
        EXCEPT
        -- All customers who have a transaction in the last 2 months
        SELECT DISTINCT CustNum
        FROM #orders
        WHERE Orderdate >= DATEADD(month,-2,GETDATE())
    ) dat

DROP TABLE #orders

Based on the fact that a customer table is available, this can also be a solution:

CREATE TABLE #orders(OrderId int identity(1,1), CustNum int, Orderdate date)

-- using system columns to populate demo data (I'm lazy)
INSERT INTO #orders(CustNum,Orderdate)
    SELECT system_type_id, DATEADD(month,column_id*-1,GETDATE())
    FROM sys.all_columns

CREATE TABLE #customers(CustNum int)

-- Populate customer table with demo data
INSERT INTO #customers(CustNum)
    SELECT DISTINCT custNum 
    FROM #orders

-- Possible Solution 2:
SELECT 
    COUNT(*) as noTransaction
FROM #customers as c
LEFT JOIN(
        -- All customers who have a transaction in the last 2 months
        SELECT DISTINCT CustNum
        FROM #orders
        WHERE Orderdate >= DATEADD(month,-2,GETDATE())
    ) t
    ON c.CustNum = t.CustNum
WHERE t.CustNum IS NULL

DROP TABLE #orders
DROP TABLE #customers

You'll receive a counted value of each customer which hasn't bought anything in the last 2 months. As I've read it, you try to run this query regularly (maybe for a special newsletter or something like that). If you won't count, you'll getting the customer numbers which can be used for further processes.

Solution with rolling months After clearing the question, this should make the thing you're looking for. It generates an output based on rolling months.

CREATE TABLE #orders(OrderId int identity(1,1), CustNum int, Orderdate date)

-- using system columns to populate demo data (I'm lazy)
INSERT INTO #orders(CustNum,Orderdate)
    SELECT system_type_id, DATEADD(month,column_id*-1,GETDATE())
    FROM sys.all_columns

CREATE TABLE #customers(CustNum int)

-- Populate customer table with demo data
INSERT INTO #customers(CustNum)
    SELECT DISTINCT custNum 
    FROM #orders

-- Possible Solution with rolling months:
-- first of all, get all available months
-- this can be also achieved with an temporary table (which may be better)
-- but in case, that you can't use an procedure, I'm using the CTE this way.
;WITH months AS(
    SELECT DISTINCT DATEPART(month,orderdate) as allMonths, 
                    DATEPART(year,orderdate) as allYears
    FROM #orders
)
SELECT m.allMonths,m.allYears, monthyCustomers.noBuyer
FROM months m
OUTER APPLY(
        SELECT N'01/'+m.allMonths+N'/'+m.allYears as monthString, COUNT(c.CustNum) as noBuyer
        FROM #customers as c
        LEFT JOIN(
                -- All customers who have a transaction in the last 2 months
                SELECT DISTINCT CustNum
                FROM #orders
                -- to get the 01/01/2015 out of 03/2015
                WHERE Orderdate BETWEEN DATEADD(month,-2,
                                            CONVERT(date,N'01/'+CONVERT(nvarchar(max),m.allMonths)
                                            +N'/'+CONVERT(nvarchar(max),m.allYears)))
                                -- to get the 31/03/2015 out of the 03/2015                     
                                AND DATEADD(day,-1,
                                        DATEADD(month,+1,CONVERT(date,N'01/'+
                                            CONVERT(nvarchar(max),m.allMonths)+N'/'+
                                            CONVERT(nvarchar(max),m.allYears))))
                                -- NOTICE: the conversion to nvarchar is needed
                                -- After extracting the dateparts in the CTE, they are INT not DATE
                                -- A explicit conversion from INT to DATE isn't allowed
                                -- This way we cast it to NVARCHAR and convert it afterwards to DATE

            ) t
            ON c.CustNum = t.CustNum
        WHERE t.CustNum IS NULL
            -- optional: Count only users which were present in the counting month.
            AND t.CustRegdate >= CONVERT(date,N'01/'+CONVERT(nvarchar(max),m.allMonths)+N'/'+CONVERT(nvarchar(max),m.allYears))
    ) as monthyCustomers
ORDER BY m.allYears, m.allMonths

DROP TABLE #orders
DROP TABLE #customers