0

I have this simple query that retrieving data:

select leave_detail_id, 
       emp_id, 
       leave_type, 
       leave_current_status_id, 
       leave_start_date, 
       leave_end_date
from tbl_emp_leave_details eld

Data:

enter image description here

Now I have this query, i want to filter my record based on date:

select leave_detail_id, 
       emp_id, 
       leave_type, 
       leave_current_status_id, 
       leave_start_date, 
       leave_end_date
from tbl_emp_leave_details eld
where leave_start_date >= '2019-09-01'
and   leave_end_date <= '2019-09-31'

Now it should bring the highlighted record but it shows nothing (no record found). What should I do bring the highlighted record? what is the problem with my query?

  • Look at the second criteria in your WHERE clause: '2019-09-31' <= leave_end_date. Selected rows don't satisfy this condition as all; as all the three rows have their leave_end_date value as 10 Sept 2019, which is less than 31 Sept 2019, while you are looking for cases where the leave_end_date is more than 31 Sept 2019. All being said, what exactly are you trying to achieve ? In question, explain the business logic that you are trying to achieve, and may be query can be reformulated. – Madhur Bhaiya Sep 12 at 7:16
  • You are right i have changed the query. I just want to count the total leave days of specific month. Actually the leave is submitted with start and end date. And the leave period can be span on several months (like the highlighted case in picture) so i want to calculate specific month leave that how many leaves are occur in specific month from total leave. – Muhammad Faizan Khan Sep 12 at 7:22
2

If you allow partial overlapping (not full include only as your code did) then you must compare (start1 with end2) and (start2 with end1):

where leave_end_date >= '2019-09-01'
  and leave_start_date <= '2019-09-31'

If you need the length of overlapping period then you need in

select datediff(greatest(leave_start_date, '2019-09-01'), 
                least(leave_end_date, '2019-09-31'))
0

Taking some clues from your comment (below the question):

Actually the leave is submitted with start and end date. And the leave period can be span on several months (like the highlighted case in picture) so i want to calculate specific month leave that how many leaves are occur in specific month from total leave

So basically, if both the "input start date" values are less than both the leave_start_date and less_end_date values, that means the leave does not fall in the input range. Similarly, if the "input end date" value is greater than both of them, then also, the leave does not fall in the input range. So, we can use this "negative" condition, to consider cases where leave falls in the range:

select leave_detail_id, 
       emp_id, 
       leave_type, 
       leave_current_status_id, 
       leave_start_date, 
       leave_end_date
from tbl_emp_leave_details
where 
  NOT (
       (leave_start_date < '2019-09-01' AND leave_end_date < '2019-09-01') 
        OR 
       (leave_start_date > '2019-09-31' AND leave_end_date > '2019-09-31') 
      )

Here is a similar answer of mine on StackOverflow giving further explanation: https://stackoverflow.com/a/53319378/2469308


Now, if it is ensured that leave_end_date will always be >= to the leave_start_date (data sanity is ensured), then we can condense the query further (thanks to suggestion by @Akina):

select leave_detail_id, 
       emp_id, 
       leave_type, 
       leave_current_status_id, 
       leave_start_date, 
       leave_end_date
from tbl_emp_leave_details
where 
  NOT (
       leave_end_date < '2019-09-01'
        OR 
       leave_start_date > '2019-09-31'
      )
  • @Akina thanks. added your suggestion to the answer – Madhur Bhaiya Sep 12 at 8:02
  • not (A or B) == (not A) and (not B). OR degrades query performance. – Akina Sep 12 at 8:04
0

If the start < end, then there is a simpler way to check for overlap:

WHERE `leave_start_date` <= '2019-09-31'
  AND `leave_end_date`   >= '2019-09-01'

No OR, no LEAST, etc.

Do check the inequalities to make sure it works for edge cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.