CODEWITHSUNDEEP
phpmysqlselectrangehour
Jhonatan Sandoval
Jhonatan Sandoval

Reputation: 1293

SELECT QUERY to get the assistance of employees, in a range of hours

I'm getting some troubles making a QUERY like this:

I have this table, mt_reporteradial:

enter image description here

I need the follow result in SELECT QUERY, establishing a range of hours (for ex.: between 11h and 15h):

idmtpersonalr               11           12           13           14           15
      3                     -            -            -            X            -
      5                     -            -            -            X            X
      7                     -            -            -            X            X

where indicador='S' means the 'X' in the result, and ìndicador='N'means the '-'

Is it possible just with MySQL? or i need to make it with php too?

Greetings.

Edited: I have the answer:

My updated table:

enter image description here

The QUERY:

SELECT
    distinct(idmtpersonalr),
CASE (SELECT indicador FROM mt_reporteradial WHERE idmtpersonalr = m.idmtpersonalr AND hora='13' AND fecha='2014-02-10')
        WHEN 'S' THEN 'X'
        WHEN 'N' THEN '-'
    END as 13_hr,
    CASE (SELECT indicador FROM mt_reporteradial WHERE idmtpersonalr = m.idmtpersonalr AND hora='14' AND fecha='2014-02-10')
        WHEN 'S' THEN 'X'
        WHEN 'N' THEN '-'
    END as 14_hr,
    CASE (SELECT indicador FROM mt_reporteradial WHERE idmtpersonalr = m.idmtpersonalr AND hora='15' AND fecha='2014-02-10')
        WHEN 'S' THEN 'X'
        WHEN 'N' THEN '-'
    END as 15_hr,
    CASE (SELECT indicador FROM mt_reporteradial WHERE idmtpersonalr = m.idmtpersonalr AND hora='16' AND fecha='2014-02-10')
        WHEN 'S' THEN 'X'
        WHEN 'N' THEN '-'
    END as 16_hr
FROM
    mt_reporteradial m

Upvotes: 1

Views: 52

Answers (2)

Mario
Mario

Reputation: 196

You already have the answer, but to avoid subqueries...

SELECT idmtpersonalr, 
       MAX(IF(hora=11,IF(indicador='S','X',IF(indicador='N','-','')),'')) `11`,
       MAX(IF(hora=12,IF(indicador='S','X',IF(indicador='N','-','')),'')) `12`,
       MAX(IF(hora=13,IF(indicador='S','X',IF(indicador='N','-','')),'')) `13`,
       MAX(IF(hora=14,IF(indicador='S','X',IF(indicador='N','-','')),'')) `14`,
       MAX(IF(hora=15,IF(indicador='S','X',IF(indicador='N','-','')),'')) `15`
FROM mt_reporteradial
/*WHERE fecha='2014-02-10'*/
GROUP BY idmtpersonalr
ORDER BY idmtpersonalr

Upvotes: 0

MonkeyZeus
MonkeyZeus

Reputation: 20747

Something Like this might work:

SELECT
    distinct(idmtpersonalr),
    CASE (SELECT indicador FROM mt_reporteradial WHERE idmtpersonalr = m.idmtpersonalr AND hora='11')
        WHEN 'S' THEN 'X'
        WHEN 'N' THEN '-'
    END CASE as 11_hr,
    CASE (SELECT indicador FROM mt_reporteradial WHERE idmtpersonalr = m.idmtpersonalr AND hora='12')
        WHEN 'S' THEN 'X'
        WHEN 'N' THEN '-'
    END CASE as 12_hr,
    CASE (SELECT indicador FROM mt_reporteradial WHERE idmtpersonalr = m.idmtpersonalr AND hora='13')
        WHEN 'S' THEN 'X'
        WHEN 'N' THEN '-'
    END CASE as 13_hr,
    CASE (SELECT indicador FROM mt_reporteradial WHERE idmtpersonalr = m.idmtpersonalr AND hora='14')
        WHEN 'S' THEN 'X'
        WHEN 'N' THEN '-'
    END CASE as 14_hr,
    CASE (SELECT indicador FROM mt_reporteradial WHERE idmtpersonalr = m.idmtpersonalr AND hora='15')
        WHEN 'S' THEN 'X'
        WHEN 'N' THEN '-'
    END CASE as 15_hr
FROM
    mt_reporteradial m
WHERE
    fecha = '2014-02-10'

Upvotes: 1

Related Questions