SQL determine averages based on open and closed dates

Question

I have an oder table (in MySQL DB) that lists the open and closed date and time for orders. For order that are open the closed date and time is blank.

Here is an example of the table:

Order   OPEN                    CLOSED  
1       2002-06-17 18:37:27     2002-07-05 16:37:27
2       2003-05-16 18:37:27     2003-06-28 16:37:27
3       2004-06-15 18:37:27     2004-07-23 16:37:27
4       2003-07-14 18:37:27     2003-07-18 16:37:27
5       2002-08-13 18:37:27     2002-09-27 16:37:27
6       2004-09-12 18:37:27     2005-07-19 16:37:27
7       2005-10-11 18:37:27     2006-07-18 16:37:27
8       2006-11-10 18:37:27     2006-12-06 16:37:27
9       2007-12-11 18:37:27     2008-07-04 16:37:27
10      2009-01-12 18:37:27     
11      2015-02-13 18:37:27     2015-07-17 16:37:27

I would like to create a query that would tell me by day the number of orders that I have open. I then want to average the number open each day of the month so that I can look at the month or month trend of open orders.

I know that I could query for any day and determine the number of open orders, so I could create a program that would do this for each days and then insert the daily totals into a database that I could then average.

I would like to know if there is a pure SQL solution to this. Is there a way with the table above to write a query that the results would be the average number of open orders by month?

Yes there is: https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html — Marc B, Aug 21 '15 at 19:19

score 1 · Answer 1 · answered Aug 21 '15 at 19:24

1

To get the daily orders:

SELECT COUNT(*), DATE(open) FROM orders WHERE close IS NULL GROUP BY DATE(open)

answered Aug 21 '15 at 19:24

Gabi Lee

1,193
8
13

That doesnt solve the OP issue. Because orders doesnt close until the close date. Other days is open. – Juan Carlos Oropeza Aug 21 '15 at 19:51

score 1 · Answer 2 · edited May 23 '17 at 11:51

First you need a days table so you could do a left join

Using select Generate days from date range

This use store procedure in MSQL

Then you do something like this

SQL Fiddle Demo

I have to modify your sample data to one month because I only create one month days table.

select day_id, count(order_id) total_open
from 
(
    select *
    from  
        days d left join
        orders o 
            on  d.day_value >= date(o.o_open)
            and (
                   o.o_close is null -- still open
                or d.day_value <= o.o_close
            )
    order by day_id
) as open_byday
group by day_id

This consider an order open for day D if you close the order that day. I mean was open that day for a while before you close it.

Otherwise you have to change to or d.day_value < o.o_close

SQL determine averages based on open and closed dates

2 Answers2