Mysql query on dates!

[rameshfaj]

I have a table with entries:

id as int,/////////////////primary key

leave_start_date  as date,/////////his leave starts on this day

period as int.///////his leave will last for this number of days

 

Now I have some entries like(1,2007-06-29,10) according to the above mentioned order.

Now when the user has the current date(2007-07-07) then how could I make my query to get some data that tellls that the employee has leave in this month also.

[fenway]

Say again?

[PFMaBiSmAd]

I don't exactly understand what "the employee has leave in this month also" should match either, but the following should (untested) find the rows of the people that have leave on the date being tested (replace CURDATE() with any date you want to get the records for) -

 

SELECT your_columns, DATE_ADD(leave_start_date, INTERVAL `period` DAY) as end_date
FROM your_table
WHERE CURDATE() BETWEEN leave_start_date AND end_date

 

To literally find the rows of the people that had any leave in the month (and year) that is being tested (replace CURDATE() with any date you want to get the records for) -

 

SELECT your_columns, LEFT(leave_start_date, 7) as start_yyyymm,
LEFT(DATE_ADD(leave_start_date, INTERVAL `period` DAY), 7) as end_yyyymm,
LEFT(CURDATE(), 7) as cur_yyyymm
FROM your_table
WHERE cur_yyyymm BETWEEN start_yyyymm AND end_yyyymm

[fenway]

I prefer EXTRACT( YEARMONTH from yourField ) rather than using string functions... it's more explicit and future-proof.

[rameshfaj]

Actually what i have is a leave table named leisure.It has the leave_start_date and the period fields.The period may extend for other(next 1 or more) months also.

Now I need to have the values for `period` in following cases:

DATE(ADDDATE(leave_start_date,INTERVAL period DAY) can lie in this month only or extend to other months.Then I need to have the value of period in current month.

Ex:

if leave_start_date='2008-06-29' and period=35 then when the currentdate is provided for month 6 then the period should be 2,when the currentdate is provided for month 7 the period should have value of 31 and when the currentdate is provided for month 8 the period should be 2 .

Is it possible to do this in a single query?

[fenway]

Sure, use CASE.