SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Internet
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Retrieve rows older than 3 work days

    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:
    Code MySQL:
    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:
    Code MySQL:
    SELECT 
    	COUNT(`id`)
    FROM 
    	`quotes`
    WHERE 
    	`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.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Internet
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Quote Originally Posted by guido2004 View Post
    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?


Tags for this Thread

Bookmarks

Posting Permissions

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