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

ats2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Here is what I would try.
<?php

$now  = date('c', strtotime('Yesterday'));
$then = date('c', strtotime("Today + $day_sel Days"));

$sql 
= 
"
SELECT id, fullname, department, firstdate, returndate, comment from timeofflog
WHERE firstdate BETWEEN '$now' AND '$then'
AND status = 'Approved' 
AND deleted = 'no'
ORDER BY department, fullname
"
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonCommented:
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
0
ats2012Author Commented:
This worked perfect. Thank you
0
Ray PaseurCommented:
Great!  Thanks for the points, ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.