Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    3

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

    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

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

  3. #3
    Join Date
    Nov 2006
    Posts
    3
    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

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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
    Last edited by Tyveleyn; 11-28-06 at 10:51.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •