Get list of users between a date range using mySQL Query

I have a time off form that i use and i have an absentee list that users can go to and browse to see who is off. I created a query that pulls all the records by the first date there gone. Below is what my query looks like. It will pull everyone that is off for the next 7 days. The issue I have is I have another field that is there return date. Right now if a user is gone lets say August 15th and there return date is August 20th my query wont list them after the 15th. What do i need to add to list these users? Thanks


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

Open in new window

maximus81Asked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
Choose the scenarios you want to cover and use OR:
/*
  Various Scenarios:
         now             now + 7
          |=================|
 a  <--------|                      returndate BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND DATE_ADD(NOW(), INTERVAL 6 DAY)
 b                       |------->  firstdate BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND DATE_ADD(NOW(), INTERVAL 6 DAY)
 c     |-------------------------|  firstdate < DATE_ADD(NOW(), INTERVAL -1 DAY) AND returndate > DATE_ADD(NOW(), INTERVAL 6 DAY)
 d             |--------|           firstdate BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND DATE_ADD(NOW(), INTERVAL 6 DAY)

 */

Open in new window

0
 
maximus81Author Commented:
This is what i am testing and kinda confused. How I have it setup is the user can select to list everyone that is off from Today, the next 7 days and then the Next 30 days.

SELECT id, fullname, department, firstdate, returndate, comment from timeofflog
where
returndate BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND DATE_ADD(NOW(), INTERVAL 6 DAY) or
firstdate BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND DATE_ADD(NOW(), INTERVAL 6 DAY)
and status = 'Approved'
order by department, fullname
0
 
lwadwellCommented:
>> How I have it setup is the user can select to list everyone that is off from Today, the next 7 days and then the Next 30 days.

Now I am confused.  What is the question/issue?

Where did 30 days come from?  I assume the "INTERVAL 6" would change to "INTERVAL 29".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.