Link to home
Create AccountLog in
Avatar of ats2012
ats2012

asked on

Issues with mySQL Date query

I setup an absentee page that displays all the users that are out. I setup a query to pull all the users gone for todays date. You can also select 7 and 30 days. The issue I am running into is if a user took off from 09/06 and returns on 09/15. The query would only show him being gone on 09/06. The next day if you look at the report it will not display them.

Here is the query. I know I have to do something with the return date field but having issues getting it working.

SELECT id, fullname, department, firstdate, returndate, comment from timeofflog
where 
firstdate BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND DATE_ADD(NOW(), INTERVAL $day_sel DAY)
and status = 'Approved' and deleted = 'no'
order by department, fullname

Open in new window

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Tomas Helgi Johannsson
Hi!

This is due to the fact that you have a time-window range on firstdate with the range "yesterday" to "a in the future date from now + some days".
As the days go by your time window slides showing different date range and data as the date range did  before. :)

You can have another "time-window" for the returndate and let these thow time sets intersect for some time-period if that is what you are looking for.

Something like this. (this needs to be adjusted to your needs :)  )
SELECT id, fullname, department, firstdate, returndate, comment from timeofflog
where 
firstdate BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND DATE_ADD(NOW(), INTERVAL $day_sel DAY)
and returndate BETWEEN DATE_ADD(NOW(), INTERVAL + 7 DAY) AND DATE_ADD(NOW(), INTERVAL $day_sel+ 7 DAY)

and status = 'Approved' and deleted = 'no'
order by department, fullname
                                  

Open in new window




Regards,
    Tomas Helgi
Avatar of ats2012
ats2012

ASKER

This worked perfect. Thank you
Great!  Thanks for the points, ~Ray