Using MySQL (without a server side technology) is there anyway to retrieve rows from a database which are older than 3 working days (Mon - Fri)?
The table looks like this:
CREATE TABLE `quotes` (
`id` int(8) NOT NULL auto_increment,
`q_id` varchar(5) collate latin1_general_ci NOT NULL default '',
`customer_name` varchar(255) collate latin1_general_ci NOT NULL default '',
`category` varchar(5) collate latin1_general_ci NOT NULL default '',
`status` enum('Open','Closed','Awaiting Customer','Ordered') collate latin1_general_ci NOT NULL default 'Open',
`q_recieved` varchar(10) collate latin1_general_ci NOT NULL default '',
`q_finished` varchar(10) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (`id`)
The basic query I am trying to achieve is similar to:
`q_recieved` < (NOW() - 259200)
AND `status` = 'Open'
However, the above only gets rows older than 3 days ago. So if I call this on a Monday then it will only gets rows older than the previous Friday, however I would like it to get rows older than the previous Wednesday. Likewise, if I call this query on a Thursday I would like to to retrieve rows older than the previous Tuesday.
Sorry if my explanation is weak, I am finding it really hard to describe. Basically, it should not take into account anything from a Saturday or Sunday.
MySQL is server side technology
How about festivities? They aren't working days either. I guess you'll have to have a calendar table that indicates which are working days and which are not.
And why don't you use a DATE format for your date columns. It would make working with dates a lot easier.
Sorry, I meant server-side like php or asp.
Personally I would prefer to use the DATE format for the date columns however it is an old project written by someone else which I am having to edit.
How would I use a calendar table in MySQL?