How to show date rows equals current date?

I got this error sir

PHP Code:

Error: Every derived table must have its own alias 


@jelz2018,

Almost there... add the red part: as d.  I needed to represent the from source as a table.  I named the table d.

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
) as d
where DATE(concat(year,'-',month,'-',1st_date)) >= CURDATE() and DATE(concat(year,'-',month,'-',2nd_date)) <= CURDATE()

Sir the code is working without error now, but still not recognizing date range. eg March 6-8, 2019.

@jelz2018,

Awesome! Thats Great! Almost there. What does the query kick out for that date?

@jelz2018,

I see the issue. Your ranges have single digit days instead of two digit days. Instead of 06-08 it has 6-8. Ok, we will have to dig a little deeper.

Thanks sir for assisting me on this one I really appreciate it.

@jelz2018,

Can you do me a favor and run the query below and show a few rows of the output.

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

@jelz2018,

Here you go.  I'm heading to bed but I'll take a look at your response after I get some rest.

Code:

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
) as d
where curdate() between (DATE(concat(year,'-',month,'-',1st_date)) - INTERVAL 1 DAY) and (DATE(concat(year,'-',month,'-',2nd_date))
+ INTERVAL 1 DAY)

Hello sir this the result when running the first code

Attached Files

Thumbnail(s)
   

This is the result when running the 2nd code

Attached Files

Thumbnail(s)