• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to show date rows equals current date?

#21
I got this error sir
PHP Code:
ErrorEvery derived table must have its own alias 
Reply

#22
@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()
Reply

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

#24
@jelz2018,

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

#25
@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.
Reply

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

#27
@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
Reply

#28
@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)
Reply

#29
Hello sir this the result when running the first code


Attached Files Thumbnail(s)
   
Reply

#30
This is the result when running the 2nd code


Attached Files Thumbnail(s)
   
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.