CODEWITHSUNDEEP
phpmysqlsql
Eldho NewAge
Eldho NewAge

Reputation: 1333

MySql - Get next date which satisfy a condition

I have a mysql table called 'helper_leaves' in which user have entered their leave dates for the next 2 months.

I would like to get 2 upcoming working dates for a particular user_id which are NOT in the 'helper_leaves' table and also should be next to the current date.

**id | user_id | leave_date** 

 1   |   1     | 2016-07-07 
 2   |   1     | 2016-07-09 
 3   |   1     | 2016-07-15 
 4   |   1     | 2016-08-03

I want write a query to get next 2 working dates of user_id = 1, from now. Please note, this table is having only the leave dates. But I want to get the next 2 availability dates .

For the sample data given, I expected to get 2016-07-14 and 2016-07-16 as the next 2 working dates because today is 2016-07-13

Note: All the dates which are not in this table is considered as a working date.

Please help!!!!

Upvotes: 1

Views: 463

Answers (2)

Bhavin Solanki
Bhavin Solanki

Reputation: 1364

Can you please try with below query? In that, i have just generate next 60 dates and exclude leave_date from that with limit 2.

SELECT CURDATE() + INTERVAL a + b DAY dte
FROM
 (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
    UNION SELECT 8 UNION SELECT 9 ) d,
 (SELECT 0 b UNION SELECT 10 UNION SELECT 20 
    UNION SELECT 30 UNION SELECT 40) m
WHERE CURDATE() + INTERVAL a + b DAY  <  DATE_ADD(CURDATE(),INTERVAL 60 DAY) and dte not in ( select leave_date from helper_leaves where user_id = 1)
ORDER BY a + b limit  2

Upvotes: 2

Kunal
Kunal

Reputation: 604

For this you can use interval

For Ex:

SELECT DATE_ADD(leave_date, INTERVAL 1 DAY) AS workingDay;

Upvotes: 0

Related Questions