How to show date rows equals current date?

@jelz2018

Here you go...

select count(employee_id) as count
from (
select employee_id,year,month,
      case when LOCATE('-',multiple) > 0 then substring(multiple,1,locate('-',multiple) - 1)
      else multiple end as 1st_date,
      case when LOCATE('-',multiple) > 0 then substring(multiple,locate('-',multiple) + 1,length(multiple) - locate('-',multiple))
      else multiple end as 2nd_date
from ats_leave_apps
)
where DATE(concat(year,'-',month,'-',1st_date)) >= CURDATE() and DATE(concat(year,'-',month,'-',2nd_date)) <= CURDATE()

You can test the from source by running it separately.  

Code:

select employee_id,year,month,
      case when LOCATE('-',multiple) > 0 then substring(multiple,1,locate('-',multiple) - 1)
      else multiple end as 1st_date,
      case when LOCATE('-',multiple) > 0 then substring(multiple,locate('-',multiple) + 1,length(multiple) - locate('-',multiple))
      else multiple end as 2nd_date
from ats_leave_apps

This will create the from and to date from the multiple date range field.


Then in your final query you can use that source query (in the code window)  to get your counts.