The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
$getAbsences = mysql_query("SELECT respid, startDate, returnDate FROM fm_calendar_absenceRequest WHERE SUBSTRING(startDate,1,10) <= '".date("Y-m-d", strtotime($dayChoice))."'
AND
IF(partialDay = 1(SUBSTRING(returnDate,1,10) >= '".date("Y-m-d", strtotime($dayChoice))."'))
IF(partialDay = 1(SUBSTRING(returnDate,1,10) >= '".date("Y-m-d", strtotime($dayChoice), '-1 day')."')) AND hidden = '0' AND requestApproved = '1'") or die("Check Time: " . mysql_error());
SELECT respid, startDate,
CASE WHEN partialDay = 1 THEN SUBSTRING(returnDate - INTERVAL 1 DAY, 1, 10)
ELSE SUBSTRING(returnDate, 1, 10)
END as returnDate
FROM fm_calendar_absenceRequest
WHERE SUBSTRING(startDate,1,10) <= '2013-06-24'
AND hidden = '0' AND requestApproved = '1'
If you want to post a few examples of the inputs and expected outputs, we may be able to give you a tested-and-working example.http://sscce.org/
There are different scenarios all of which I need to account for.If you can post some of the test data set that shows these different scenarios, we can almost certainly help you further. Descriptions of the test data are almost always useless; the actual data is all we need.
SELECT respid, startDate,
CASE WHEN partialDay <> 1 THEN SUBSTRING(returnDate - INTERVAL 1 DAY, 1, 10)
ELSE SUBSTRING(returnDate, 1, 10) -- i.e. if partial day use stored returnDate
END as returnDate
FROM fm_calendar_absenceRequest
WHERE hidden = '0' AND requestApproved = '1'
AND (
(
CURDATE() between startDate and returnDate -- i.e. if partial day use stored returnDate
and partialDay = 1
)
OR
(
CURDATE() between startDate and (returnDate - INTERVAL 1 DAY)
and partialDay <> 1
)
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.
I'm assuming partialDay is a field in the table.
Open in new window