I'm trying to get a listing of everything between two dates, including those items with start and end dates before and after a specific date.
Example:
SELECT * FROM expenses WHERE (userid = '#' AND exstartdate >= '2015-07-25'
AND exenddate <= '2015-08-24') OR (userid = '#' AND reg='Yes')
ORDER BY eamount DESC LIMIT 0, 25
eid ename eamount userid exstartdate exenddate paid reg
1 Bill 1 1000 # 2015-01-01 2015-11-01 no Yes
17 bill 17 300 # 2015-07-29 2015-07-29 no No
2 Bill 2 85 # 2013-11-01 2017-12-31 no Yes
6 Lunches 80 # 2015-01-01 2016-12-31 no Yes
I'm trying to get this in as well:
3 Bill 3 87.00 1 Yes 2015-01-01 2016-05-01 no No
but can't seem to do it unless I reverse the > and <. Bills 1, 2 and lunch are showing up because of the reg flag.
I'm wondering if this would be better to do with PHP instead.
Edit: To clarify, I'm looking to get all items that are between $date1 (Seen above as 2015-07-25) and $date2 (2015-05-24) from the table. The table has start dates and end dates that if the date from the table includes the date listed in $date1 and $date2.
So if $date1(2015-07-25) is after exstartdate(2015-01-01) and $date2(2015-08-24) is before exenddate(2015-11-01) then it shows the result.
Also show if exstartdate and exenddate is between $date1 and $date2.
Basically, if the dates between exstartdate and exenddate also match a date between $date1 and $date2, then show result.
According to your description you want:
SELECT * FROM expenses
WHERE userid = '#' AND
((exstartdate >= '2015-07-25' AND exenddate <= '2015-08-24') OR
(exstartdate < '2015-07-25' AND exenddate > '2015-08-24') OR
reg='Yes')
ORDER BY eamount DESC LIMIT 0, 25
There's a HUGE pile of overlap cases for two date ranges. complete overlap, no overlap at all, partial overlap with all kinds of before/after variations. e.g. your fields in the db are a and b, and the date range you're testing are x and y:
a b
x y - simple, no overlap at all
x y - simple, no overlap at all
x y - x outside, y inside/on
x y - x outside, y inside/on
x y - x outside, y inside/on
x y - complete overlap
x y - y outside, x inside/on
x y - y outside, x inside/on
x y - y outside, x inside/on
xy - x = y = a
xy - x = y = b
xy - complete overlap
It gets ugly, so... which of these cases do you need?
You're trying to find all rows where the interval of exstartdate to extenddate overlaps with the two dates you're providing. The row that you're missing is failing to match because one interval is inside the other. You will need something like this:
WHERE
exstartdate BETWEEN a AND b
OR
exenddate BETWEEN a AND b
OR
(exstartdate <= a AND exenddate >= b)
Where a and b are your interval start and end dates. You should keep in mind such queries can be very slow with even a thousand records.