?
Solved

IF statement in WHERE clause of MySQL Query

Posted on 2013-06-21
9
Medium Priority
?
362 Views
Last Modified: 2013-07-01
I'm trying to pull absence requests to a page and in the query, we store the return to work date...

If it is a partial day off, I want to display the return date that is stored in the database, but if it is not a partial day< I want to pull one day less than the return date.

So for example if I took off from 6-18 and my return date is 6-21, on todays calendar view, I will not be listed...however if I took off on 6-21 for three hours, then I should display on the calendar.

I'm not sure where I'm at with the query below, all I know is it doesn't work.

$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());

Open in new window

0
Comment
Question by:t3chguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39267845
seems to me that you need a case expression within the select clause (this is the sql IF equivalent). Currently you are trying to embed some if conditions into the where clause (and IF isn't a sql thing).

I'm assuming partialDay is a field in the table.
$getAbsences = mysql_query("SELECT respid, startDate, 
 CASE WHEN partialDay = 1 THEN SUBSTRING(returnDate - INTERVAL 1 DAY, 1, 10) 
      WHEN partialDay = 0 THEN SUBSTRING(returnDate, 1, 10) 
 END  as returnDate 
 FROM fm_calendar_absenceRequest 
 WHERE SUBSTRING(startDate,1,10) <= '".date("Y-m-d", strtotime($dayChoice))."' 
 AND hidden = '0' AND requestApproved = '1'") or die("Check Time: " . mysql_error());

-- as straight sql it would look like this:

SELECT respid, startDate, 
 CASE WHEN partialDay = 1 THEN SUBSTRING(returnDate - INTERVAL 1 DAY, 1, 10) 
      WHEN partialDay = 0 THEN 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'

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39267849
you might prefer to use 'else'
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'

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39268078
See if this article can give you some clues about it.  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://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:t3chguy
ID: 39271245
PortletPaul,

The query you had suggested returns ALL events prior to 6/24, instead of the 22 absences that it should be returning.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39271320
>> 22 absences that it should be returning.
I was trying to answering the following really:

"If it is a partial day off, I want to display the return date that is stored in the database, but if it is not a partial day< I want to pull one day less than the return date."
and for this you need something like the case expression I provided

To figure out "22 absences that it should be returning" would I guess require a change to the where clause - but I don't know you data.  How does one arrive at the 22 expected absences? (I can help translate that into sql if needed).

e.g. should the where clause be something like?
 WHERE SUBSTRING(startDate,1,10) >= '2013-06-01'
AND hidden = '0' AND requestApproved = '1'

bottom line: I'm guessing how to arrive at the 22 expected results
- & I didn't know that was part of the question
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39271338
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/

Really, it's all about the data.
0
 
LVL 1

Author Comment

by:t3chguy
ID: 39271346
Sure, sorry.

Essentially we are trying to display the absences for that particular day.

There are different scenarios all of which I need to account for.

1.  If an employee took a partial day (which is a flag in the table), they need to show up for being off today.

2.  If an employees return date is today, then they shouldn't be, which is where I was trying to use the CASE statement for if its a partial day, then show up, but if its not a partial day, then somehow exclude that record from the result date, since they are back to work today.

3.  If an employee is out for an extended period of time, for example started their vacation on 6/22 and returns 7/1, they should appear on this list even though the start and end date is out of this range.

The report is currently done on another server, but the guy before me ran multiple queries and used PHP to help manipulate the data, where I was hoping for an all MySQL solution, but maybe thats not possible?  I'm not sure.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39271479
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.

An example of the data might take the form of a short script that created a temporary table and inserted the data into the table.  The SQL INSERT statements need not be many or complicated - just enough data to illustrate each of the scenarios you want to treat differently.

Armed with a little script that does those things, we can almost certainly show you tested and working code examples.  Thanks, ~Ray
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39271671
on today's calendar view...
does this help?
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
       )

Open in new window

0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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