Mysql – Compare a date withing range of date fields in thesql

MySQL

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:

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?

Best Answer

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'))

Related Solutions

MySQL – How to Select Max(Date) from Union Query

So it turns out that confluence uses a different structure to manage this data and now the discrepancies appear to be resolved.

Here is the query:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
 SELECT 
    u.user_name, 
    u.first_name as First_Name, 
    u.last_name as Last_Name, 
    date_format(l.successdate, '%Y-%m-%d') as Last_Login 
  FROM confluence.logininfo l 
  JOIN confluence.user_mapping m ON m.user_key = l.username 
  JOIN confluence.cwd_user u ON m.username = u.user_name 
  JOIN confluence.cwd_directory d ON u.directory_id = d.id and u.active = 'T' 
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc

Mysql – Record doesn’t show if selecting earlier date interval

I think the problem with you query is the use of aggregated functions: sum and count. Using group by category, subcategory shows only rows with different category and subcategory values: only one row for each category and subcategory.

You can add comments column on your group by clause. Another option is to remove group by clause, sum and count functions, but this second option generates a result without aggregates values.

I hope this helps

Miquel Boada

Best Answer

Related Solutions

MySQL – How to Select Max(Date) from Union Query

Mysql – Record doesn’t show if selecting earlier date interval

Related Question