SQL Statement

dcjones

Hi all,

I can't get my head around this, what I am trying to do is;

If the current date is in between accomarival AND accomdepartdate THEN 'Away' ELSE 'Home' END AS loc, or if the current date is in between a_arrival_2 AND a_depart_2 THEN 'Away' ELSE 'Home' END AS loc2, the code I am using id this.

SELECT *, CASE WHEN CURDATE() 
BETWEEN accomarival AND accomdepartdate 
THEN 'Away' ELSE 'Home' END AS loc,
OR a_arrival_2 AND a_depart_2 
THEN 'Away' ELSE 'Home' END AS loc2,
DATE_FORMAT(departuredate, '%d-%m-%Y') as d_date,     

DATE_FORMAT(accomarival, '%d-%m-%Y') as a_date,     

DATE_FORMAT(accomdepartdate, '%d-%m-%Y') as ac_date,     

DATE_FORMAT(profiledate, '%d-%m-%Y') as p_date,
DATE_FORMAT(a_arrival_2, '%d-%m-%Y') as a2_date, 
DATE_FORMAT(a_depart_2, '%d-%m-%Y') as d2_date 
FROM members
WHERE member_id = colname

I can get the following to work

SELECT *, CASE WHEN CURDATE() 
BETWEEN accomarival AND accomdepartdate
OR a_arrival_2 AND a_depart_2 
THEN 'Away' ELSE 'Home' END AS loc,
DATE_FORMAT(departuredate, '%d-%m-%Y') as d_date,     

DATE_FORMAT(accomarival, '%d-%m-%Y') as a_date,     

DATE_FORMAT(accomdepartdate, '%d-%m-%Y') as ac_date,     

DATE_FORMAT(profiledate, '%d-%m-%Y') as p_date,
DATE_FORMAT(a_arrival_2, '%d-%m-%Y') as a2_date, 
DATE_FORMAT(a_depart_2, '%d-%m-%Y') as d2_date 
FROM members
WHERE member_id = colname

but when I try to change this part i get an error.

BETWEEN accomarival AND accomdepartdate 
THEN 'Away' ELSE 'Home' END AS loc,
OR a_arrival_2 AND a_depart_2 
THEN 'Away' ELSE 'Home' END AS loc2,

Can anyone see where I am going off track.

j_70

What is the error message?

dcjones

Hi,

Sorry about that, I forgot to put it in the post.

MySQL error 1064

Thanks

cgraz

In the first query, you only have one CASE statement. In the second query, you are attempting to use two CASE statements but only actually use CASE once. Take a look at your code below. I added the SELECT , CASE... part so you could see it all. I also indented to make it easier for you to catch the error.

SELECT *, 
   CASE 
      WHEN CURDATE() BETWEEN accomarival AND accomdepartdate
      THEN 'Away' 
      ELSE 'Home' 
   END AS loc,
      OR a_arrival_2 AND a_depart_2
      THEN 'Away' 
      ELSE 'Home' 
   END AS loc2,

Notice How you END the case, but don't start another one, yet your last line has an END. Using OR implies you want one case statement as you have in your first query, but I assume that's not what you want since you're rewriting a working query. You also have no comparisons for a_arrival2 or a_depart_2. i.e.

CASE
WHEN a_arrival_2 = 'some value' AND a_depart_2 = 'some other value'
THEN...
ELSE...
END

I'm not really sure what you're trying to do differently in this query than in your first one, so I can't be of much more help without additional information.