Link to home
Start Free TrialLog in
Avatar of David11011
David11011Flag for United States of America

asked on

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.
Avatar of Member_2_861731
Member_2_861731
Flag of Canada image

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David11011

ASKER

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

Open in new window