This is an illustration of what we want to achieve.
Given members and their transaction date. We want to find the average latency of members. To get the average latency, we would need to get the difference in days, between member's 1st and 2nd visit, 2nd and 3rd visit, 3rd and 4th visit, etc. The average of these differences will be the average latency of the members.
To do this on SQL, we can rank the member's transaction based on the TransactDate, and then self-join to the same set of records, but shift the ranking by one order. This way, we will get the difference of days between a transaction and the subsequent transaction.
--Create temp table CREATE TABLE #Transact ( MemberID NVARCHAR(100), RecieptNo NVARCHAR(100), TransactDate DATE ) --Insert dummy data INSERT INTO #Transact VALUES ('M001', 'R001', '2015/11/20') INSERT INTO #Transact VALUES ('M001', 'R002', '2015/12/30') INSERT INTO #Transact VALUES ('M001', 'R003', '2016/01/05') INSERT INTO #Transact VALUES ('M002', 'R004', '2015/09/12') INSERT INTO #Transact VALUES ('M002', 'R005', '2015/09/15') INSERT INTO #Transact VALUES ('M002', 'R006', '2015/09/25') INSERT INTO #Transact VALUES ('M002', 'R007', '2015/10/30')
Here are the SQL queries to get the Latency per member:
--Store the ranked transaction into temp table. Alternately, use common table expression SELECT MemberID, TransactDate, RankNo = ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY TransactDate) INTO #1 FROM #Transact --Get the average latency per member SELECT MemberID, Latency = AVG(CONVERT(NUMERIC(18,2), Latency)) FROM ( SELECT a.MemberID, Latency = DATEDIFF(DAY, a.TransactDate, b.TransactDate) FROM #1 a INNER JOIN #1 b ON b.MemberID = a.MemberID AND b.RankNo = (a.RankNo + 1) ) aa GROUP BY aa.MemberID
That's all folks!