Thread: Time Between a Customer's Transaction

1. Registered User
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. Registered User
Join Date
Nov 2004
Posts
1,428
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"

3. Registered User
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 !