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

#11
I need all the days included in the date range sir. Like for example if an employee leave let say March 7-9, 2019 he will be on the dashboard for days 7,8 and 9. I'm ok which one you will suggest sir as long as it will work. I really appreciate for your generous time.
Reply

#12
Hi,

I would suggest you change the structure of your table to have 2 date columns (start_date and end_date).
This way you can use simple date functions from PHP or MySQL to built your query.
A good decision is based on knowledge and not on numbers. - Plato

Reply

#13
(03-05-2019, 11:22 PM)salain Wrote: Hi,

I would suggest you change the structure of your table to have 2 date columns (start_date and end_date).
This way you can use simple date functions from PHP or MySQL to built your query.

If I will change it I need to alter a lot of codes which in return will take enough time to complete. Because not only the Leave Administration needed this but also the other modules in our system.
Reply

#14
@jelz2018,

There is no need to add/create another date field. We can achieve the goal through SQL.
Reply

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

#16
Sir php_rocs this shows when I use the codes.
PHP Code:
MySQL saidDocumentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case when LOCATE('-',multiple) > 0 then substring(multiple,locate('-',multiple) ' at line 5 
Reply

#17
@jelz2018,

Sorry about that. What version of MySQL are you using? Also, can you show the actual code as well (I know it should be as I typed for you but just to make sure).
Reply

#18
PHP 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
)
where DATE(concat(year,'-',month,'-',1st_date)) >= CURDATE() and DATE(concat(year,'-',month,'-',2nd_date)) <= CURDATE()
 
LIMIT 025 
MySQL said
Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case when LOCATE('-',multiple) > 0 then substring(multiple,locate('-',multiple) ' at line 5 

MySQL Version: 5.0.11
Reply

#19
@jelz2018,

I found the problem. If you used the bold code then I forgot to add the comma at the end of the first case when condition. I added it (it's the red comma). You should be good to go now.

Please added a comma at the end of 1st_date. That will fix your error.
Reply

#20
No it's okay sir, I'm really thankful for helping me on this. It's highly appreciated.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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