Single DB query for fetching the business days (mysql)

tags:

mysql-query

views:

answers:

Single DB query for fetching the business days (mysql)

Hi,

I would like to know if it's possible to fetch X no of business days (date datatype) via a single DB call in mysql.

The list of holidays are stored in a table. So, the days (starting from CURDATE) which do not have entries in that table are considered to be working days.

Thanks!

+1 A:

Does the holiday table also include weekends?

Create a numbers table with a single column (num, say) and rows 1 through some-large-value - this'll come in handy. LEFT JOIN the holidays table to this table on "holidayday" = (CURDATE + INTERVAL num - 1 DAY), add a WHERE to exclude the holidays and then order this query by num ascending and LIMIT the query to the X rows.

Will A 2010-07-10 14:51:26

It may or may not include weekends. Only the days in the holiday table would be considered as holidays.Was thinking about your solution. For the above solution to work, all the days would need to be present in the holiday table with a flag to indicate whether that day is a holiday or not. Correct?Also, is there a way to do this without creating any other table?Thanks!

2010-07-10 16:13:10

That weekends may or may not be present seems fair. You don't need all of the days to be present in the holiday table (the query would be much more straightforward if this were the case - no need for an additional table) - so long as your query takes rows from the numbers table and performs a left join to the holiday table, you'll get all days which you can then filter using a suitable WHERE clause. I don't know of a way of doing this in MySQL without a separate table - then again, I don't work with MySQL as much as, say, SQL Server, where it is possible-but-awkward without such a table.

Will A 2010-07-10 16:24:16

ansaurus

tags:

views:

answers:

Single DB query for fetching the business days (mysql)

related questions