Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Location
    Orlando, FL
    Posts
    2

    Unanswered: Time Between a Customer's Transaction

    I have a table of customer transactions. Customers may have any number of transactions, 1, 5, 100. Each transaction is date/time stamped as to when it happened. We have a lot of customers that have not had a transaction in a while. It is easy to list the customers whose last transaction is, say greater than 545 days or 18 months.

    What has been asked is, what percent of customers have a long time between transactions? If we look at customers with no activity in 18 months what percent of them will come back. So of the customers that we have, how many have an 18 month gap between transactions.

    I am thinking that I first need to calculate the time between transactions, and then refine that to see how many had more than an 18 month gap.

    Sounds easy? If the data looks like

    CustomerNo DateTimeOfTrans
    1 2009-05-03
    1 2009-06-04
    2 2009-11-01
    2 2009-12-04 **
    2 2011-10-16 **
    2 2011-11-08
    3 2009-07-06
    3 2009-08-17
    4 2011-05-23
    4 2011-08-03
    4 2011-11-24

    The difference between these two transactions (**) is significant.

    Is there an easy way to calculate the time difference between the dates within a customers set of transactions?

    Andy

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should get you started:
    Code:
    CREATE TABLE #DaTable(
    	CustomerNo	INT	NOT NULL,
    	DateTimeOfTrans	DATETIME	NOT NULL
    )
    
    INSERT INTO #DaTable(CustomerNo, DateTimeOfTrans) VALUES
    (1, '2009-05-03'), (1, '2009-06-04'), 
    (2, '2009-11-01'), (2, '2009-12-04'), (2, '2011-10-16'), (2, '2011-11-08'), 
    (3, '2009-07-06'), (3, '2009-08-17'), 
    (4, '2011-05-23'), (4, '2011-08-03'), (4, '2011-11-24')
    
    ;WITH CTE AS
    (SELECT CustomerNo, 
    	DateTimeOfTrans, 
    	ROW_NUMBER() OVER (PARTITION BY CustomerNo ORDER BY DateTimeOfTrans DESC) as RowNum
    FROM #DaTable)
    SELECT LastTrans.CustomerNo, DATEDIFF(month, PrevTrans.DateTimeOfTrans, LastTrans.DateTimeOfTrans) as MonthsNoTrans
    FROM CTE as LastTrans
    	INNER JOIN CTE as PrevTrans On
    		LastTrans.CustomerNo = PrevTrans.CustomerNo AND
    		LastTrans.RowNum = PrevTrans.RowNum - 1
    WHERE DATEDIFF(month, PrevTrans.DateTimeOfTrans, LastTrans.DateTimeOfTrans) > 18
    Last edited by Wim; 03-28-12 at 17:49. Reason: removed " AND LastTrans.RowNum >= 1"
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Mar 2012
    Location
    Orlando, FL
    Posts
    2

    WOW- Thanks !

    That more than got me started. Looks like it is giving me the base for what we are looking for.

    Thanks again !

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •