Welcome Guest, Not a member yet? Register   Sign In
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

(This post was last modified: 03-06-2019, 09:06 PM by php_rocs.)

@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

(This post was last modified: 03-06-2019, 10:16 PM by php_rocs.)

@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

(This post was last modified: 03-07-2019, 07:20 AM by php_rocs.)

@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




Theme © iAndrew 2016 - Forum software by © MyBB