+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: How to show date rows equals current date? (/showthread.php?tid=72921)
RE: How to show date rows equals current date? - jelz2018 - 03-06-2019
I got this error sir
PHP Code:
Error: Every derived table must have its own alias
RE: How to show date rows equals current date? - php_rocs - 03-06-2019
@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()
RE: How to show date rows equals current date? - jelz2018 - 03-06-2019
Sir the code is working without error now, but still not recognizing date range. eg March 6-8, 2019.
RE: How to show date rows equals current date? - php_rocs - 03-06-2019
@jelz2018,
Awesome! Thats Great! Almost there. What does the query kick out for that date?
RE: How to show date rows equals current date? - php_rocs - 03-06-2019
@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.
RE: How to show date rows equals current date? - jelz2018 - 03-06-2019
Thanks sir for assisting me on this one I really appreciate it.
RE: How to show date rows equals current date? - php_rocs - 03-06-2019
@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
RE: How to show date rows equals current date? - php_rocs - 03-06-2019
@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)RE: How to show date rows equals current date? - jelz2018 - 03-07-2019
Hello sir this the result when running the first code
RE: How to show date rows equals current date? - jelz2018 - 03-07-2019
This is the result when running the 2nd code