[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get list of users between a date range using mySQL Query

Posted on 2012-08-16
3
Medium Priority
?
380 Views
Last Modified: 2012-08-30
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

0
Comment
Question by:maximus81
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38300642
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
 

Author Comment

by:maximus81
ID: 38300723
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
 
LVL 25

Expert Comment

by:lwadwell
ID: 38303022
>> 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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 19 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question