How do you find the predicted next order date based on previous order dates?

sysman20 · #1 (**permalink**) 11-26-06, 18:26

Hi,

All history data resides in a informix 9.21 data warehouse
There is a table that lists all the sales order history information for customers. Based on the number of days between previous historical orders, I am required to predict when the customer is likely to order again.

e.g
CustomerID SalesOrderID OrderDate

1 1 02/10/2005
1 2 04/10/2005
1 3 07/12/2005
1 4 02/12/2006
etc..

It is required to find the days between each order and the preceeding one to calculate the average number of days between

orders and then this is added to the last order date to predict the next potential order date.

Is there any way this can be done in SQL or any analytic or OLAP functions in informix?

Any help greatly appreciated.

thanks
sysman20

Tyveleyn · #2 (**permalink**) 11-27-06, 10:01

Hi,

The way to calculate the averages (in days) between the various orderdates per customer and sales order in SQL could be:

Code:

SELECT customer_id, sales_order_id, 
        avg(extend(order_date,YEAR TO DAY) - extend( 
        (SELECT order_date FROM table b                          
        WHERE b.customer_id = a.customer_id                      
        AND b.sales_order_id = a.sales_order_id -1),YEAR TO DAY))
FROM table a                                                     
WHERE customer_id = 1                                           
AND sales_order_id > 1                                           
GROUP BY 1, 2                                                       
ORDER BY 1, 2

This way all sales orders after the first one are selected with the number of days between the current and previous sales order.
If you want to use only the last interval you can add:

Code:

AND NOT EXISTS (SELECT 1 FROM table c           
        WHERE c.customer_id = a.customer_id     
        AND c.sales_order_id > a.sales_order_id)

to the query.
If you want to use this number to add to e.g. the current date you can extend the SELECT clause with:

Code:

SELECT customer_id, sales_order_id,                                      
        avg(extend(order_date,YEAR TO DAY) - extend(                     
        (SELECT order_date FROM table b                                  
        WHERE b.customer_id = a.customer_id                              
        AND b.sales_order_id = a.sales_order_id -1),YEAR TO DAY)) + today

Regards

sysman20 · #3 (**permalink**) 11-27-06, 11:27

Tyveleyn thanks for your reply.
Since the Sales Order ids are not sequential, the sub query does not return any rows. I tried to use FIRST 1, but found out that this cannot be used in a sub query or in a view definition.

SELECT customer_id, sales_order_id,
avg(extend(order_date,YEAR TO DAY) - extend(
(SELECT order_date FROM table b
WHERE b.customer_id = a.customer_id
AND b.sales_order_id = a.sales_order_id -1),YEAR TO DAY))
FROM table a
WHERE customer_id = 1
AND sales_order_id > 1
GROUP BY 1, 2
ORDER BY 1, 2

Do you think I can write a function to return the next order date here? Sorry about all these questions, I am only familiar with oracle and sql server, this is a first with informix

SELECT customer_id, sales_order_id,
avg(extend(order_date,YEAR TO DAY) - extend(
(GetNextOrderDate(customer_id,sales_order_id OR order_date),YEAR TO DAY))
FROM table a
WHERE customer_id = 1
AND sales_order_id > 1
GROUP BY 1, 2
ORDER BY 1, 2

thanks,
sysman20

Tyveleyn · #4 (**permalink**) 11-28-06, 06:31

Hi,
I think this query delivers what you're asking:

Code:

SELECT customer_id,                                                            
        avg(extend(order_date,YEAR TO DAY) - extend(                           
        (SELECT max(order_date) FROM table b                                   
        WHERE b.customer_id = a.customer_id                                    
        AND b.order_date < a.order_date),YEAR TO DAY)) + order_date
FROM table a                                                                   
WHERE NOT EXISTS (SELECT 1 FROM table c                                          
        WHERE c.customer_id = a.customer_id                                    
        AND c.order_date > a.order_date)                               
GROUP BY 1

But I certainly would make a stored function for this, if alone because by this the logic is stored in the database and accessible by query tools and applications.

Regards

Internet Services

Web Development

Digital Marketing

Consumer Tech