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

MySQL ServerPHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Ray Paseur

ASKER CERTIFIED SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
ASKER
ats2012

This worked perfect. Thank you
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Ray Paseur

Great!  Thanks for the points, ~Ray