David11011

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.

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.

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
```

Let us know if this works or at least gives you an idea of where to start.

Open in new window