# Average number of months SQL

I want to find the average amount of months between each of our customer's orders.

I know that I can use DATEDIFF(MONTH, <date1>, <date2>)  but I need to find the average between several orders.

If I have these dates in the table
2012-09-15
2012-05-12
2011-11-08
2011-04-26

It was 4 from between the most recent order and the previous order.  6 months between that order and the one before that. 7 between the last two orders.
The average of 4,6,7 is 5.6 which rounds up to 6 months.

With this information I will know to send a reminder email to the customer 6 months after his last order.
Member_2_861731

I gave this a try but not entirely sure if the table structure resembles what I built.
Let us know if this works or at least gives you an idea of where to start.

``````declare @T table (
Customer int,
Field date)

insert into @T
select 1,'20120915'
UNION
select 1,'20120512'
UNION
select 1,'20111108'
UNION
select 1,'20110426'
UNION
select 2,'20120815'
UNION
select 2,'20120612'
UNION
select 2,'20111208'
UNION
select 2,'20110826'

SELECT Customer, ROUND(AVG(CAST(Diff AS FLOAT)),0) AS Average
FROM (
SELECT T.Customer, T.Field, DATEDIFF(M,T.Field,(SELECT TOP 1 Field FROM @T T2 WHERE T2.Field>T.Field AND T2.Customer = T.Customer)) as Diff
FROM @T T
WHERE DATEDIFF(M,T.Field,(SELECT TOP 1 Field FROM @T T2 WHERE T2.Field>T.Field AND T2.Customer = T.Customer)) IS NOT NULL
) newT
GROUP BY Customer``````
Patrick Matthews

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

Brilliant! Thanks Patrick!

Here was my original code for this. It took like 20 minutes to run through all 350k customers. I knew there was a better and and you got it spot on.

``````DECLARE @RESULTS AS TABLE (
CUSTNO CHAR(10),
AVGDIFF INT
)
-- GET UNIQUE CUSTOMER NUMBERS WITH ASSOCIATED ROW NUMBER FOR LOOPING
SELECT	ROW_NUMBER() OVER(ORDER BY CUSTNO) AS ROWNUM,
CUSTNO
INTO	#CUSTLIST
FROM	#ORDERITEMS
GROUP BY CUSTNO

--DECALRE LOOP COUNTERS
DECLARE @CUSTCOUNTER AS INT
DECLARE @ORDERCOUNTER AS INT

--LOOP THROUGH EACH CUSTOMER
SET @CUSTCOUNTER = 1
WHILE @CUSTCOUNTER <= (SELECT COUNT(*) FROM #CUSTLIST) BEGIN
--WHILE @CUSTCOUNTER <= 2 BEGIN

--GET CURRENT CUSTOMER NUMBER
DECLARE @CUSTNO AS CHAR(10)
SET @CUSTNO = (SELECT CUSTNO FROM #CUSTLIST WHERE ROWNUM = @CUSTCOUNTER)

-- GET UNIQUE ORDER DATES WITH ASSOCIATED ROW NUMBER FOR LOOPING
SELECT	ROW_NUMBER() OVER(ORDER BY ENTRYDATE) AS ROWNUM,
CUSTNO,
ENTRYDATE
INTO	#ORDERSLIST
FROM	#ORDERITEMS
WHERE	CUSTNO = @CUSTNO

-- GET NUMBER OF ORDERS FOR THE CUSTOMER
DECLARE @NUMBEROFORDERS AS INT
SET @NUMBEROFORDERS = (SELECT COUNT(*) FROM #ORDERSLIST WHERE CUSTNO = @CUSTNO)

--DECLARE TABLE TO CALCULATING THE AVERAGE DATE DIFFERENCES
DECLARE @AVGTBL AS TABLE (
CUSTNO CHAR(10),
AVGDIFF INT
)
--LOOP THROUGH EACH ORDER DATE AND CALCULATE THE GAP
SET @ORDERCOUNTER = 1
WHILE @ORDERCOUNTER <= @NUMBEROFORDERS BEGIN

-- CHECK TO SEE IF THIS IS THE LAST ORDER
IF @ORDERCOUNTER != @NUMBEROFORDERS BEGIN
DECLARE @DATE1 AS SMALLDATETIME
DECLARE @DATE2 AS SMALLDATETIME
SET @DATE1 = (SELECT ENTRYDATE FROM #ORDERSLIST WHERE ROWNUM = @ORDERCOUNTER)
SET @DATE2 = (SELECT ENTRYDATE FROM #ORDERSLIST WHERE ROWNUM = @ORDERCOUNTER + 1)

--PRINT CAST(@CUSTNO AS NVARCHAR(50)) + ' - ' + CAST(@DATE1 AS NVARCHAR(50)) + ' - ' + CAST(@DATE2 AS NVARCHAR(50)) + ' - ' + CAST(DATEDIFF(MONTH, @DATE1, @DATE2) AS NVARCHAR(50))
INSERT INTO @AVGTBL VALUES(@CUSTNO, DATEDIFF(MONTH, @DATE1, @DATE2))
END

SET @ORDERCOUNTER = @ORDERCOUNTER + 1
END

DROP TABLE #ORDERSLIST
INSERT INTO @RESULTS VALUES(@CUSTNO, (SELECT AVG(AVGDIFF) FROM @AVGTBL WHERE AVGDIFF > 0))

SET @CUSTCOUNTER = @CUSTCOUNTER + 1
END

SELECT * FROM @RESULTS

DROP TABLE #CUSTLIST``````