mysql select records where begin and end is within 2 business days

Question

I have a single table requests

| ID | Opened              | Closed              |
| 1  | 2012-08-21 16:11:22 | 2012-10-31 11:48:58 |
| 2  | 2012-08-15 16:17:19 | 2012-09-18 09:31:32 |

And want to write a query that will check the table for all records that were closed within 2 business days

I was thinking something like

SELECT * FROM requests WHERE Closed <= (Opened+TWO BUSINESS DAYS)

But cannot figure out the logic for the TWO BUSINESS DAYS part.

You could have a look here: http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates — Tim, Oct 31 '12 at 22:17

score 0 · Accepted Answer · answered Nov 01 '12 at 16:46

0

I actually found that using a calendar table was best. (As much as I didn't want to) http://www.brianshowalter.com/calendar_tables has a great tool for doing this!

answered Nov 01 '12 at 16:46

yakimaweb

13
2

score -1 · Answer 2 · answered Oct 31 '12 at 22:18

-1

Use Between: SELECT * FROM requests WHERE Opened BETWEEN '06-Jan-1999' AND '10-Jan-1999' AND Closed BETWEEN '06-Jan-1999' AND '10-Jan-1999'

http://www.1keydata.com/es/sql/sql-between.php

http://www.w3schools.com/sql/sql_between.asp

answered Oct 31 '12 at 22:18

Carlos Sing Ramos

1

score -1 · Answer 3 · answered Oct 31 '12 at 22:18

-1

I think this is what you mean: You want to select all the records in which the closed date is between the opened date and opened date + 2

    SELECT *
  FROM requests
 WHERE closed BETWEEN opened AND opened + 2

answered Oct 31 '12 at 22:18

Roberto Navarro

948
4
16

`DATETIME` + 2 does not evaluate to a new `DATETIME` two days later than the first. Besides, "+ 2 days" is not the same as "+ 2 _business_ days". – lanzz Oct 31 '12 at 22:24
I ran the same SQL against some sample data in my environment and I found several cases in which the SQL statement returns valid rows...so I don't agree with your statement that "+ 2 does not evaluate to a new DATETIME". As far as the 2 business days, that's why I prefaced my statement with "I think this is what you mean". He's yet to reply to my statement to clarify the issue.. – Roberto Navarro Oct 31 '12 at 22:44
`SELECT now() + 2` returns `20121101090435.000000`, which is certainly _not_ a `DATETIME` refering to two days in the future. You _might_ be getting _some_ rows, but they would hardly be satisfying the condition of being within two days from each other, and certainly won't be within two _business_ days from each other. The term "business days" has a quite well defined meaning, which is not equivalent to "days". The actual business days might vary from region to region, but the _meaning_ of the term is clear. – lanzz Nov 01 '12 at 07:06

mysql select records where begin and end is within 2 business days

3 Answers3