MySQL Case语句双重结果

Question

Good day! I'm working with my project that need to indicate if the hours is present, leave, holiday. If employee is present, I count the time from in to out. and if leave and holiday is payable the total payable time is their working schedule.

Select `leave`.emp_id,
                    leave_request.date,
                    CASE  WHEN  leave.status_id = 4 AND leave_request.with_pay = 1  THEN 
                    ( 
                      SELECT TIMESTAMPDIFF(HOUR, w_s.time_in, w_s.time_out) 
                      FROM working_schedule w_s
                      JOIN employee_working_schedule ews ON w_s.id = ews.working_schedule_id
                      WHERE ews.employee_id = '01-0001' 
                    ) 
                    WHEN leave.status_id = 4 AND leave_request.with_pay = 0  THEN '0'
                    END AS status               
                    FROM `leave`
                    JOIN leave_request On `leave`.id = leave_request.`leave_id`
                    JOIN employee_logs AS e_l ON leave.emp_id = e_l.employee_id
                    WHERE `leave`.emp_id = '01-0001'    
                    UNION
                    SELECT e.employee_code AS id, 
                    CAST(e_l.time_in As date) day,
                    CASE    WHEN e_l.time_in IS NOT NULL THEN TIMESTAMPDIFF(HOUR, e_l.time_in, e_l.time_out)

                    FROM employee e
                    LEFT JOIN employee_logs e_l ON e.employee_code = e_l.employee_id
                    LEFT JOIN employee_company e_c ON e.employee_code = e_c.employee_id
                    LEFT JOIN company c ON e_c.company_id = c.id
                    JOIN employee_working_schedule ews On e.employee_code = ews.employee_id
                    JOIN working_schedule w_s On ews.working_schedule_id = w_s.id
                    WHERE e.employee_code = '01-0001' 
                    AND e_l.time_in BETWEEN '2017-09-01' AND '2017-09-28'
                    UNION
                    SELECT '01-0001',
                    CASE WHEN holiday.date BETWEEN '2017-09-01' AND '2017-09-28' THEN
                    (SELECT holiday.date) END AS date,
                    CASE WHEN holiday.date BETWEEN '2017-09-01' AND '2017-09-28' THEN 
                    ( 
                      SELECT TIMESTAMPDIFF(HOUR, w_s.time_in, w_s.time_out) 
                      FROM working_schedule w_s
                      JOIN employee_working_schedule ews ON w_s.id = ews.working_schedule_id
                      WHERE ews.employee_id = '01-0001' 
                    ) 
                    END AS status
                    FROM holiday    

So is it possible here to add another result. For example (CASE WHEN e_l.time_in IS NOT NULL THEN TIMESTAMPDIFF(HOUR, e_l.time_in, e_l.time_out) END AS hours_count) THEN Present END AS status

Answer 1

If I understand you correctly, you want to add another column for your select statement for status. Maybe what you need to do is like this.

SELECT `leave`.emp_id,
       leave_request.date,
       CASE 
            WHEN leave.status_id = 4 AND leave_request.with_pay = 1 THEN (
                     SELECT TIMESTAMPDIFF(HOUR, w_s.time_in, w_s.time_out)
                     FROM   working_schedule w_s
                            JOIN employee_working_schedule ews
                                 ON  w_s.id = ews.working_schedule_id
                     WHERE  ews.employee_id = '01-0001'
                 )
            WHEN leave.status_id = 4 AND leave_request.with_pay = 0 THEN '0'
       END AS hours_count,
       CASE 
            WHEN leave.status_id = 4 AND leave_request.with_pay = 1 THEN 'Paid'
            WHEN leave.status_id = 4 AND leave_request.with_pay = 0 THEN 'Unpaid'
       END + ' Leave' AS Status
FROM   `leave`
       JOIN leave_request
            ON  `leave`.id = leave_request.`leave_id`
       JOIN employee_logs  AS e_l
            ON  leave.emp_id = e_l.employee_id
WHERE  `leave`.emp_id = '01-0001'
UNION

SELECT e.employee_code            AS id,
       CAST(e_l.time_in AS DATE) AS Date,
       CASE 
            WHEN e_l.time_in IS NOT NULL THEN TIMESTAMPDIFF(HOUR, e_l.time_in, e_l.time_out)
       END AS hours_count,
       'Present' AS Status
FROM   employee e
       LEFT JOIN employee_logs e_l
            ON  e.employee_code = e_l.employee_id
       LEFT JOIN employee_company e_c
            ON  e.employee_code = e_c.employee_id
       LEFT JOIN company c
            ON  e_c.company_id = c.id
       JOIN employee_working_schedule ews
            ON  e.employee_code = ews.employee_id
       JOIN working_schedule w_s
            ON  ews.working_schedule_id = w_s.id
WHERE  e.employee_code = '01-0001'
       AND e_l.time_in BETWEEN       '2017-09-01' AND '2017-09-28'
UNION

SELECT '01-0001',
       CASE 
            WHEN holiday.date BETWEEN '2017-09-01' AND '2017-09-28' THEN (
                     SELECT holiday.date
                 )
       END  AS DATE,
       CASE 
            WHEN holiday.date BETWEEN '2017-09-01' AND '2017-09-28' THEN (
                     SELECT TIMESTAMPDIFF(HOUR, w_s.time_in, w_s.time_out)
                     FROM   working_schedule w_s
                            JOIN employee_working_schedule ews
                                 ON  w_s.id = ews.working_schedule_id
                     WHERE  ews.employee_id = '01-0001'
                 )
       END  AS hours_count,
       'Holiday' AS Status
FROM   holiday    

Just add another column beside hours_count column. Hope this help.