CODEWITHSUNDEEP
phpmysql
pato.llaguno
pato.llaguno

Reputation: 741

Selecting dynamic columns Mysql

I'm having a pretty hard time into figuring out how to ask this. I have 2 tables, one is a list of 'Orders' with the registry time, and another table 'Activity' where i have a register of the time these Orders made it to a process.

What i dont get how to do is the next thing, i need to be able to determine with a query the amount of time for each Order since the last Process.

Table: Ordenes

| Id | Numero | flujo_id | LastOrder |            FechaRegistro |
|----|--------|----------|-----------|--------------------------|
|  1 |    111 |        1 |         0 | August, 13 2015 13:25:00 |
|  2 |    222 |        1 |         2 | August, 13 2015 13:25:00 |
|  3 |    333 |        1 |         4 | August, 13 2015 13:25:00 |
|  4 |    444 |        1 |         3 | August, 13 2015 13:25:00 |

Table: Actividad

| Id |             TiempoInicio | TiempoFin | Proceso_Id | Orden_Id |
|----|--------------------------|-----------|------------|----------|
|  7 | August, 13 2015 13:30:00 |    (null) |          1 |        2 |
|  8 | August, 13 2015 13:50:00 |    (null) |          2 |        2 |
|  9 | August, 13 2015 13:30:00 |    (null) |          1 |        3 |
| 10 | August, 13 2015 13:50:00 |    (null) |          2 |        3 |
| 11 | August, 13 2015 14:20:00 |    (null) |          3 |        3 |
| 12 | August, 13 2015 14:25:00 |    (null) |          4 |        3 |
| 13 | August, 13 2015 13:30:00 |    (null) |          1 |        4 |
| 14 | August, 13 2015 13:50:00 |    (null) |          2 |        4 |
| 15 | August, 13 2015 14:20:00 |    (null) |          3 |        4 |

Table: Procesos

| Id | Estacion_Id |                                             Nombre |                 TiempoStd |
|----|-------------|----------------------------------------------------|---------------------------|
|  1 |           1 |      Informacion <br/>tecnica para <br/>aprobacion | January, 01 1970 00:02:00 |
|  2 |           2 | Aprobacion de<br/> Informacion<br/>Tecnica cliente | January, 01 1970 00:24:00 |
|  3 |           3 |             Informacion<br/>Tecnica de<br/>Proceso | January, 01 1970 00:04:00 |
|  4 |           4 |           Compra y<br/>Recepcion de<br/>Materiales | January, 01 1970 02:00:00 |

Expected Output.

| Id | Numero |            FechaRegistro |   P1   |   P2   |   P3   |   P4   |
|----|--------|--------------------------|--------|--------|--------|--------|
|  1 |    111 | August, 13 2015 13:25:00 | (null) | (null) | (null) | (null) |
|  2 |    222 | August, 13 2015 13:25:00 | 5:00   | 20:00  | (null) | (null) |
|  3 |    333 | August, 13 2015 13:25:00 | 5:00   | 20:00  | 30:00  | 5:00   |
|  4 |    444 | August, 13 2015 13:25:00 | 5:00   | 20:00  | 30:00  | (null) |

What i dont know how to do is being able to do the call dynamically so that i would get P1,P2,P3, until Pn columns n = the number of rows on 'Procesos' table. And for the time calculation i do have an idea, but since I dont know how to get these columns i can't try it.

Here is a fiddle with the tables loaded up. HERE

Upvotes: 1

Views: 91

Answers (1)

Arun Palanisamy
Arun Palanisamy

Reputation: 5469

I came up with something like below. It might look little fuzzy.

select ordenes.Id,Numero,FechaRegistro,m.p1,a.p2,b.p3,c.p4 from Ordenes 
left join (select ordenes.id,
              TIMESTAMPDIFF(MINUTE,Ordenes.FechaRegistro,min(Actividad.TiempoInicio )) P1 
           from Ordenes
           left join Actividad on ordenes.id=Actividad.orden_id
           group by Actividad.orden_id) as m on ordenes.id=m.id
left join (select Orden_Id,TIMESTAMPDIFF(MINUTE,min(TiempoInicio ),max(TiempoInicio )) P2 
           from actividad 
           where Proceso_Id in ('1','2')
           group by orden_id) as a  on ordenes.id=a.orden_id
left join 
(select Orden_Id,TIMESTAMPDIFF(MINUTE,min(TiempoInicio ),max(TiempoInicio )) P3 
        from actividad 
        where Proceso_Id in ('2','3')
        group by orden_id) as b on ordenes.id=b.orden_id
left join 
(select Orden_Id,TIMESTAMPDIFF(MINUTE,min(TiempoInicio ),max(TiempoInicio )) P4 
        from actividad 
        where Proceso_Id in ('3','4')
        group by orden_id) as c on ordenes.id=c.orden_id
order by id;

Output I'm getting

| Id | Numero |            FechaRegistro |   P1   |   P2   |   P3   |   P4   |
|----|--------|--------------------------|--------|--------|--------|--------|
|  1 |    111 | August, 13 2015 13:25:00 | (null) | (null) | (null) | (null) |
|  2 |    222 | August, 13 2015 13:25:00 | 5      | 20     | 0      | (null) |
|  3 |    333 | August, 13 2015 13:25:00 | 5      | 20     | 30     | 5      |
|  4 |    444 | August, 13 2015 13:25:00 | 5      | 20     | 30     | 0      |

It doesn't meet all the requirements. But it might be useful atleast as reference to proceed further

DEMO Fiddle

Upvotes: 2

Related Questions