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

#31
@jelz2018,

We're in the home stretch.  I'm definitely awake now after a good nights rest (I was starting to run on fumes).  Ok, I see what the issue was, I had one to many parenthesis.

Here you go...

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)

I fixed the previous code too.
Reply

#32
@jelz2018,

I should have done this last night it would have been easier (I probably was really tired). Anyway...here the query is with your sample data. Enjoy http://sqlfiddle.com/#!9/6831ef/1/0
Reply

#33
The code is now perfectly working sir without any error. One last hit sir, do I need to put an (AND) if let's say I will add another condition like if 'approved' column will be equal to 1 which approved leave and 2 disapproved of course I want approved = 1. Thank you for your generous heart for helping me out on this. I know positive rating of 1 is not enough. But I sincerely thank you.
Reply

#34
@jelz2018,

Yes, you can add an AND if you need to. It was my pleasure helping. Challenges like this keeps my mind engaged and I enjoy helping others. I love doing it.
Reply

#35
I'm having problem adding it. It gives me error. I added it after the last close parenthesis.
Reply

#36
@jelz2018,

Is the approved column in the ats_leave_apps table? if so, here is my solution http://sqlfiddle.com/#!9/4a8d64/2/0

I added the approved column to the FROM datasource (If I don't the select statement doesn't see the column from the datasource). This allows me to add it to the where statement.
Reply

#37
Two thumbs up sir, the code now working smoothly. Thank you a lot. I hope in the future if I need some help I can find you. God bless you more as you are very generous to others.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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