Mysql Case Statement not working?

Question

I'm trying to get the CycleTime Report for a particular process.

Here I'm getting charging time for processOrders

select processorder_number,timediff(max(process_end_time), 
min(process_start_time)) as `Charging_time`
from process_tracker where status_id=21 
group by processorder_number;

Output of Query is like this

---------+---------+----------------+
| processorder_number| charging_time| 
+--------------------+--------------+
|   1002135897       | 00:00:16     |
|   1002135897       |02:21:20      |
---------------------+---------------

Here I'm getting Packing time for processOrders

select processorder_number,timediff(max(process_end_time), 
min(process_start_time)) as `Packing_time`
from process_tracker where status_id=14 
group by processorder_number;

Output of Query is like this

---------+---------+----------------+
| processorder_number| Packing_time | 
+--------------------+--------------+
|   1002135897       | 00:11:16     |
|   1002135897       | 01:21:20     |
---------------------+---------------

For completed time the query is below

select processorder_number,max(process_end_time),min(process_start_time),
timediff(max(process_end_time), min(process_start_time)) as `Complete_time`
from process_tracker where week(process_start_time)=week(curdate())
group by processorder_number
order by processorder_number asc;

I need output like this.

+--------+---------+----------------+----------------+--------------+---------------+
| processorder_number| charging_time| packaging_time | quality_time |Completion_time|
+--------------------+--------------+----------------+--------------+---------------+
|   1002135897       | 00:00:16     |   00:11:16     | 00:21:20     | 00:33:56      |
|   1002135897       | 02:21:20     |   01:21:20     | 00:20:13     | 04:02:53      |
---------------------+--------------+----------------+--------------+---------------+

'm trying with case statements but not getting output. Only zeros is coming as output

select processorder_number,
    (CASE WHEN status_id = 21 then timediff(max(process_end_time),min(process_start_time)) else 0 END ) as charging_time,
    (CASE WHEN status_id = 14 then timediff(max(process_end_time),min(process_start_time)) else 0 END ) as Packing_time,
    (CASE WHEN status_id =18 then timediff(max(process_end_time),min(process_start_time)) else 0 END ) as quality_time
    from process_tracker group by processorder_number;

The above query is generating only zeros as output. please help me, anyone. thanks in advance

This transformation is called pivoting and has been asked and answered here on SO soooo many times. The most upvoted answer gives you a step-by-step description how to do such transformations in mysql. What you are missing is separating the conditional statements from the final group by in a subquery. Remember, when you group by by processor number, the individual statuses are lost. You should enable the strict sql mode because it would have given you an error instead of cryptic results. — Shadow, Jan 30 '18 at 07:52

Nidhi257 · Accepted Answer · 2018-01-30T07:44:16.820

0

Try executing below query:

select processorder_number,
CASE status_id
        WHEN 21 then timediff(max(process_end_time),min(process_start_time)) 
        ELSE 0 
        END as charging_time,
CASE status_id
        WHEN 14 then timediff(max(process_end_time),min(process_start_time)) 
        ELSE 0 
        END as Packing_time,
CASE status_id
        WHEN 18 then timediff(max(process_end_time),min(process_start_time)) 
        ELSE 0 
        END as quality_time
        from process_tracker group by processorder_number,status_id;

edited Jan 30 '18 at 07:44

answered Jan 30 '18 at 07:37

Nidhi257

754
1
5
23

Thanks for reply @Nidhi257, but I'm getting output as zero – Ullas Sharma Jan 30 '18 at 07:40
try adding status_id under group by clause. – Nidhi257 Jan 30 '18 at 07:44

Mysql Case Statement not working?

1 Answers1