This is the current state of my aplication, i have 3 tables, the first one is the table that takes care of the Procceses i have, (P1,P2), the second one is the Orders, this one has the information on how many rows the output should have. and the last activity gets me the actual information on the time elapsed on each process.
The Process Table has a Standard Time that the procces should take to complete, so to my output i want to add a column that will estimate the end time of all the procceses, the logic to this is that if (P1 from the order) > (than standard time of P1) true: add (P1 from the order) to FechaRegistro else:add (P1 std time) to FechaRegistro and the result of that would go to the same logic for P2, to finally get the estimated time.
Procesos Table:
| Id | TiempoStd |
|----|-----------|
| 1 | 00:02:00 |
| 2 | 00:24:00 |
Ordenes:
| Id | Numero | FechaRegistro |
|----|--------|-----------------------------|
| 1 | 111 | September, 29 2015 13:25:00 |
| 2 | 222 | September, 29 2015 13:25:00 |
Actividad:
| Id | TiempoInicio | Proceso_Id | Orden_Id |
|----|-----------------------------|------------|----------|
| 1 | September, 29 2015 13:25:00 | 0 | 1 |
| 2 | September, 29 2015 13:25:00 | 0 | 2 |
| 3 | September, 29 2015 13:30:00 | 1 | 1 |
current SQL:
SELECT
Ordenes.Id, Numero, FechaRegistro, m.P1, a.P2
FROM
Ordenes
LEFT JOIN
(SELECT
Orden_Id,
IF(MIN(TiempoInicio)=MAX(TiempoInicio),SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), now())),SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), MAX(TiempoInicio)))) as P1
FROM
Actividad
WHERE
Proceso_Id IN ('0' , '1')
GROUP BY Orden_id) AS m ON Ordenes.id = m.Orden_id
LEFT JOIN
(SELECT
Orden_Id,
IF(MIN(TiempoInicio)=MAX(TiempoInicio),SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), now())),SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), MAX(TiempoInicio)))) as P2
FROM
Actividad
WHERE
Proceso_Id IN ('1' , '2')
GROUP BY Orden_id) AS a ON Ordenes.id = a.Orden_id
ORDER BY id
current output(where xx:xx:xx is an output depending on the time now()):
| Id | Numero | FechaRegistro | P1 | P2 |
|----|--------|-----------------------------|----------|----------|
| 1 | 111 | September, 29 2015 13:25:00 | 00:05:00 | xx:xx:xx |
| 2 | 222 | September, 29 2015 13:25:00 | xx:xx:xx | (null) |
Basically i am not sure if i need to make the statement in the upper select or make another join, eighter way i just cant think of how to group the result to the final select. i am not that expirience with SQL, still learning.
this would be my expected output, ill simulate as if the time now was September, 29 2015 13:50:00 ill place the std time in the header for ref.
| Id | Numero | FechaRegistro |P1(00:02:00)|P2(00:24:00)| Cierre |
|----|--------|-----------------------------|------------|------------|-----------------------------|
| 1 | 111 | September, 29 2015 13:25:00 | 00:05:00 | 00:20:00 | September, 29 2015 13:54:00 |
| 2 | 222 | September, 29 2015 13:25:00 | 00:25:00 | (null) | September, 29 2015 14:14:00 |
I am adding this fiddle with the info loaded up
http://sqlfiddle.com/#!9/101c2/1