Welcome Guest, Not a member yet? Register   Sign In
How to show date rows equals current date?
#31

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

@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

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

@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




Theme © iAndrew 2016 - Forum software by © MyBB