CODEWITHSUNDEEP
mysqlswitch-statementcase-when
Joel
Joel

Reputation: 192

How to set a default value with MySQL "CASE WHEN" depending the result of multiple rows

Here is my problem:

with the following query, I get this result:

SELECT e.employes_id AS user_id, IFNULL(e.employes_initiales, 'Divers') AS initials, s.lieu, s.debut, s.fin, IFNULL(s.couleur, '#000000') AS color , s.id AS p_id,
CASE when couleur = '#00FF00' then 1 else 0 END as callservice
FROM ap_employes e LEFT JOIN
(ap_planing_employes sl JOIN
ap_planing s
ON sl.id_planing = s.id AND s.effacee = 0 AND
DATE('2018-04-27') BETWEEN CAST(s.debut AS DATE) AND CAST(s.fin AS DATE))
ON employes_id = sl.id_employes
WHERE e.employes_effacee = 0
ORDER BY e.employes_id ASC, FIELD(s.couleur,'#00FF00') desc, s.debut ASC, s.lieu ASC

Result 

user_id  initials  lieu                                                    debut                  fin  color      p_id  callservice  
-------  --------  ----------------------------------------  -------------------  -------------------  -------  ------  -------------
      1  DV        Test                                      2018-04-27 07:30:00  2018-04-27 07:30:00  #000000     526              0
      1  DV        Another Test                              2018-04-27 09:00:00  2018-04-27 09:00:00  #000000     504              0
      1  DV        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      2  SA        Call service                              2018-04-27 00:00:00  2018-04-28 00:00:00  #00FF00     336              1
      2  SA        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      3  SW        Customer x                                2018-04-27 00:00:00  2018-04-27 00:00:00  #000000     547              0
      3  SW        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      3  SW        End at 2pm                                2018-04-27 14:00:00  2018-04-27 14:00:00  #FF0000     538              0
      4  JE        Test2                                     2018-04-27 10:00:00  2018-04-27 10:00:00  #000000     541              0
      4  JE        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      5  FR        Holydays                                  2018-04-11 00:00:00  2018-04-29 00:00:00  #FF0000      75              0
      5  FR        Holydays                                  2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      8  IE        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
    999  Divers    (NULL)                                                 (NULL)               (NULL)  #000000  (NULL)              0

Ok, so far it's perfect

As you can see, in row 4, the last column "callservice" is 1 because the color on this line is correct according to my request

Now my problem. I would like that when nobody has the color corresponding to the call service, it is automatically assigned to the user id 3

If any user with the color corresponding to the call service, then he will have the collone "callservice" to 1. Otherwise, by default it must be the user id 3 ...

I understood how the CASE WHEN function works, but I can not apply it on several rows ...

How to do?

If my question is not perfectly clear, I can try to improve

Here is an example of the result I want to get. As you can see, nobody has the color corresponding to the callservice, so the callservice is assigned to the user ID 3

user_id  initials  lieu                                                    debut                  fin  color      p_id  callservice  
-------  --------  ----------------------------------------  -------------------  -------------------  -------  ------  -------------
      1  DV        Test                                      2018-04-27 07:30:00  2018-04-27 07:30:00  #000000     526              0
      1  DV        Another Test                              2018-04-27 09:00:00  2018-04-27 09:00:00  #000000     504              0
      1  DV        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      2  SA        Customer y                                2018-04-27 00:00:00  2018-04-28 00:00:00  #000000     336              0
      2  SA        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      3  SW        Customer x                                2018-04-27 00:00:00  2018-04-27 00:00:00  #000000     547              1
      3  SW        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              1
      3  SW        End at 2pm                                2018-04-27 14:00:00  2018-04-27 14:00:00  #FF0000     538              1
      4  JE        Test2                                     2018-04-27 10:00:00  2018-04-27 10:00:00  #000000     541              0
      4  JE        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      5  FR        Holydays                                  2018-04-11 00:00:00  2018-04-29 00:00:00  #FF0000      75              0
      5  FR        Holydays                                  2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
      8  IE        Something                                 2018-04-27 12:00:00  2018-04-27 12:00:00  #0000FF     451              0
    999  Divers    (NULL)                                                 (NULL)               (NULL)  #000000  (NULL)              0

Upvotes: 2

Views: 1367

Answers (1)

Anton
Anton

Reputation: 1057

Well, it maybe complicated solution but...

SET @couleur_number = 0;

DROP TEMPORARY TABLE IF EXISTS temp_table;
CREATE TEMPORARY TABLE temp_table ENGINE=MEMORY AS

SELECT e.employes_id AS user_id, IFNULL(e.employes_initiales, 'Divers') AS initials, 
s.lieu, s.debut, s.fin, IFNULL(s.couleur, '#000000') AS color , s.id AS p_id,
CASE when couleur = '#00FF00' then @couleur_number := @couleur_number + 1
FROM ap_employes e LEFT JOIN
(ap_planing_employes sl JOIN
ap_planing s
ON sl.id_planing = s.id AND s.effacee = 0 AND
DATE('2018-04-27') BETWEEN CAST(s.debut AS DATE) AND CAST(s.fin AS DATE))
ON employes_id = sl.id_employes
WHERE e.employes_effacee = 0
ORDER BY e.employes_id ASC, FIELD(s.couleur,'#00FF00') desc, s.debut ASC, s.lieu ASC;

SELECT user_id, initials, 
    s.lieu, s.debut, s.fin, color, p_id
CASE when @couleur_number > 0
    then
        CASE when color='#00FF00'
        then 1 else 0
        end
    else
        CASE when user_id='3'
        then 1 else 0
        end
END as callservice
 FROM temp_table;

Upvotes: 1

Related Questions