Difference between days, working days only

jussi_j

Hi,

I have in DB two days, ordered and arrived.

I would like to count what is the average time between these days but i don't want to count working days.

Maybe one efficient but not so sharp way could be:

SELECT (avg(arrived_day)-avg(order_day))*(5/7) from orders;

Do you gurus have any ideas to help me?

hand

Are these fields datetime, date, timestamp, character or integer fields?
Pleas answer this question AND give us a sample for their contents.

jussi_j

The question is about one kind of order traking system. There is orders and states for these. UI is created using PHP and the whole system is currently working as excepted. Now I would like to add some extra stuff into system. One possibility what i've been thinking is statistics, like how long it usually takes for goods to come from factory and so on.

Tables are in MySQL. Here is a dump from my table creation script.

CREATE TABLE orders(
order_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
target varchar(50) NOT NULL,
status_id TINYINT UNSIGNED NOT NULL default 1,
deliv_type_id TINYINT UNSIGNED NOT NULL default 1,
ord_type_id TINYINT UNSIGNED NOT NULL default 1,
reference varchar(20) default NULL,
order_time TIMESTAMP,
order_handler_id TINYINT UNSIGNED default NULL,
arrived DATETIME default NULL,
arrived_handler_id TINYINT UNSIGNED default NULL,
delivery_time DATETIME default NULL,
delivery_handler_id TINYINT UNSIGNED default NULL,
explnation text default NULL,
superfast_order BOOL default FALSE,
contact_name varchar(25) default NULL,
phone varchar(15) default NULL,
email varchar(25) default NULL
) TYPE=MyISAM;

I have these kind of records no more than 5000 in DB.

What i want to do is count average times from order to arrive, from order to delivery but i don't want to count saturdays or sundays ...

Thanks and cheers

tomhath

Interesting question. The only way I can think of is to get the two timestamps and loop through them (increment by the number of seconds in a day) and count how many of the timestamps format into weekdays. Kind of yucky, but it wouldn't take long or code or run.