<div dir="ltr"><div class="gmail_default" style="font-family:'courier new',monospace;color:rgb(0,0,0)">Claro, en realidad me parece como que el diseño de la solución no está bien para obtener este tipo de resultados.</div>
<div class="gmail_default" style="font-family:'courier new',monospace;color:rgb(0,0,0)">Es decir, si vos tenés entre una tarea y otra más de 5 minutos, cuanto le descontás de tiempo trabajado? 24hs - 5 min?</div>
<div class="gmail_default" style="font-family:'courier new',monospace;color:rgb(0,0,0)">
Por tanto si vos tenés que entre 2 tareas consecutivas no hay más de 5min de diferencia, podés suponer que el operador hizo el horario completo (nunca dejó de apretar enter).</div><div class="gmail_default" style="font-family:'courier new',monospace;color:rgb(0,0,0)">
En este caso, no podrías hacerlo directo con SQL y tendrías que iterar sobre las tuplas. Hacés una consulta que te devuelva todas las tuplas de un usuario ordenadas cronológicamente, luego vas recorriendo los resultados comparando el registro actual contra el siguiente y chequeando que el lapso sea menor a 5min. Si el lapso es mayor, descontás el tiempo del TOTAL (arranca en 1440). Cuando termines de procesar los registros tendrías que tener el TOTAL de minutos trabajados por el operador.</div>
</div><div class="gmail_extra"><br clear="all"><div><div><br></div>.:-:ChiCkENURiNE:-:.<div><span>+ </span><span>El <a href="http://groups.google.com/group/spam-a-lot/" target="_blank">SPAM</a> tiene su lugar.</span></div>
</div>
<br><br><div class="gmail_quote">2013/3/25 Carlos M. Martinez <span dir="ltr"><<a href="mailto:carlosmarcelomartinez@gmail.com" target="_blank">carlosmarcelomartinez@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
Pero como definis la 'duracion' de cada tarea ? el intervalo entre
dos entradas ? <br><div><div class="h5">
<br>
<div>On 3/25/13 10:50 AM, Gustavo Azambuja
wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">No, la tarea es "instantanea"... digamos, para
entender, que cada registro es una pulsación de ENTER en el
teclado del usuario... sabiendo eso, y que jamas pasan 5 minutos
sin dar ENTER a menos que no estén trabajando... como consigo
saber los "minutos trabajados por operador" (o la suma de todos
los operadores)?
<div>
<br>
</div>
<div><br>
</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">2013/3/25 Chicken Urine <span dir="ltr"><<a href="mailto:chickenurine@gmail.com" target="_blank">chickenurine@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">
<div class="gmail_default">Ok, entonces el date ese es la hora de
inicio de la tarea, por lo que supongo que la duración
de una tarea es el tiempo entre 2 registros?</div>
</div>
<div class="gmail_extra">
<div><br clear="all">
<div>
<div><br>
</div>
.:-:ChiCkENURiNE:-:.
<div><span>+ </span><span>El <a href="http://groups.google.com/group/spam-a-lot/" target="_blank">SPAM</a> tiene su lugar.</span></div>
</div>
<br>
<br>
</div>
<div>
<div>
<div class="gmail_quote">2013/3/25 Gustavo Azambuja <span dir="ltr"><<a href="mailto:hola@gazambuja.com" target="_blank">hola@gazambuja.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<p dir="ltr">La columna date tiene el datetime del
evento (q ejecutó una acción) </p>
<p dir="ltr">En definitiva tengo una lista de
eventos de diferentes usuarios y necesito
calcular los "minutos trabajados totales) </p>
<p dir="ltr">Para el cálculo asumo que si el
usuario no ejecuta eventos en 5 minutos no está
trabajando. </p>
<p dir="ltr">Uso MySQL, y podría completar el
cálculo con php. </p>
<div class="gmail_quote">El mar 25, 2013 8:20
a.m., "Chicken Urine" <<a href="mailto:chickenurine@gmail.com" target="_blank">chickenurine@gmail.com</a>>
escribió:
<div>
<div><br type="attribution">
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">
<div class="gmail_default">Gustavo, no me queda
muy claro el ejemplo. La columna date
guarda un resultado de HH:MM:ss que
llevó un tarea particular? O es la
hora de comienzo/fin?</div>
<div class="gmail_default">Que rdbms estás
utilizando? Ya que hay funciones
específicas en cada motor para el
manejo de tipo de datos date.</div>
</div>
<div class="gmail_extra">
<br clear="all">
<div>
<div><br>
</div>
.:-:ChiCkENURiNE:-:.
<div><span>+ </span><span>El <a href="http://groups.google.com/group/spam-a-lot/" target="_blank">SPAM</a> tiene
su lugar.</span></div>
</div>
<br>
<br>
<div class="gmail_quote">2013/3/24
Gustavo Azambuja <span dir="ltr"><<a href="mailto:hola@gazambuja.com" target="_blank">hola@gazambuja.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">
<div>Alguien me puede ayudar con
esta query?</div>
<div><br>
</div>
<div>básicamente, necesito
conseguir en base a una tabla
que tiene acciones realizadas
por mis usuarios (funcionarios)
durante cada minuto. Ahora,
necesito en base a esa tabla
saber los "minutos trabajados"</div>
<div><br>
</div>
<a href="http://stackoverflow.com/questions/15606675/minutes-work-by-users-how-to-make-this-query" target="_blank">http://stackoverflow.com/questions/15606675/minutes-work-by-users-how-to-make-this-query</a><br clear="all">
<div><br>
</div>
<div>
<pre style="max-height:600px;width:auto;background-color:rgb(238,238,238);margin-bottom:10px;padding:5px;vertical-align:baseline;line-height:18px;font-size:14px;overflow:auto;font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif;margin-top:0px;border:0px">
<code style="margin:0px;padding:0px;border:0px;vertical-align:baseline;font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif"><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">id</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(0,0,139)">user</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> action</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:tr ansparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> date
</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">1</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> user1</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> a1</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)"><a href="tel:2013-03-20" value="+59820130320" target="_blank">2013-03-20</a></span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">10</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">00</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">01</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">
</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">2</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> user2</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> a1</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)"><a href="tel:2013-03-20" value="+59820130320" target="_blank">2013-03-20</a></span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">10</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">00</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">03</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">
</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">3</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> user2</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> a1</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)"><a href="tel:2013-03-20" value="+59820130320" target="_blank">2013-03-20</a></span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">10</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">00</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">12</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">
</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">4</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> user3</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> a1</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)"><a href="tel:2013-03-20" value="+59820130320" target="_blank">2013-03-20</a></span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">10</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">00</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">20</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">
</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">5</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> user2</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> a1</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">,</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)"><a href="tel:2013-03-20" value="+59820130320" target="_blank">2013-03-20</a></span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent"> </span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">10</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">00</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">:</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;color:rgb(128,0,0)">24</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">
</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">....</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">
</span><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent">...</span></code></pre>
<p>
Por ejemplo:</p>
<ol>
<li style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;word-wrap:break-word">Si
tengo solo un funcionario
trabajando las 24h, tendré:
24h*60m = 1440 minutos
trabajados en el día.</li>
<li style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;word-wrap:break-word">Si
tengo 2 funcionarios durante
todo el día (24h) tendré:
2880 minutos trabajados
hoy...</li>
<li style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background-color:transparent;word-wrap:break-word">Si
tengo los mismos dos
funcionarios, pero
trabajando 12h cada uno,
vuelvo a tener los 1440
m/día.</li>
</ol>
<div><font color="#000000" face="Arial, Liberation
Sans, DejaVu Sans,
sans-serif"><span style="font-size:14px;line-height:18px">Alguna
idea de como podría armar
una query que me de ésto?
o como mucho con algo de
PHP ademas de la query.</span></font></div>
</div>
<div><font color="#000000" face="Arial, Liberation Sans,
DejaVu Sans, sans-serif"><span style="font-size:14px;line-height:18px"><br>
</span></font></div>
<div><font color="#000000" face="Arial, Liberation Sans,
DejaVu Sans, sans-serif"><span style="font-size:14px;line-height:18px">Gracias!</span></font></div>
<span><font color="#888888">
<div><br>
</div>
<div><br>
</div>
-- <br>
<font face="'trebuchet ms',
sans-serif">Gustavo Azambuja<br>
</font><a href="http://gazambuja.com" target="_blank"><font face="'trebuchet ms',
sans-serif">http://gazambuja.com</font></a>
<div>
<br>
<font face="'courier new',
monospace"><span style="font-size:x-small">Uruguay:</span></font><font face="'courier new',
monospace"><span style="white-space:pre-wrap"><span style="font-size:x-small"> </span></span></font><font face="'courier new',
monospace"><span style="font-size:x-small"><b><a href="tel:091%20300%20333" value="+59891300333" target="_blank">091
300 333</a></b>
(Montevideo - Mobile)</span></font><font face="'courier new',
monospace"><span style="font-size:x-small"><br>
</span></font></div>
</font></span></div>
<br>
_______________________________________________<br>
Uylug-programming mailing list<br>
<a href="mailto:Uylug-programming@listas.uylug.org.uy" target="_blank">Uylug-programming@listas.uylug.org.uy</a><br>
<a href="http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy" target="_blank">http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
_______________________________________________<br>
Uylug-programming mailing list<br>
<a href="mailto:Uylug-programming@listas.uylug.org.uy" target="_blank">Uylug-programming@listas.uylug.org.uy</a><br>
<a href="http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy" target="_blank">http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy</a><br>
<br>
</blockquote>
</div>
</div>
</div>
<br>
_______________________________________________<br>
Uylug-programming mailing list<br>
<a href="mailto:Uylug-programming@listas.uylug.org.uy" target="_blank">Uylug-programming@listas.uylug.org.uy</a><br>
<a href="http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy" target="_blank">http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy</a><br>
<br>
</blockquote>
</div>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
Uylug-programming mailing list<br>
<a href="mailto:Uylug-programming@listas.uylug.org.uy" target="_blank">Uylug-programming@listas.uylug.org.uy</a><br>
<a href="http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy" target="_blank">http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy</a><br>
<br>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
-- <br>
<font face="'trebuchet ms', sans-serif">Gustavo Azambuja<br>
</font><a href="http://gazambuja.com" target="_blank"><font face="'trebuchet ms', sans-serif">http://gazambuja.com</font></a>
<div>
<br>
<font face="'courier new', monospace"><span style="font-size:x-small">Uruguay:</span></font><font face="'courier new', monospace"><span style="white-space:pre-wrap"><span style="font-size:x-small">
</span></span></font><font face="'courier new', monospace"><span style="font-size:x-small"><b><a href="tel:091%20300%20333" value="+59891300333" target="_blank">091 300 333</a></b> (Montevideo -
Mobile)</span></font><font face="'courier new', monospace"><span style="font-size:x-small"><br>
</span></font></div>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
Uylug-programming mailing list
<a href="mailto:Uylug-programming@listas.uylug.org.uy" target="_blank">Uylug-programming@listas.uylug.org.uy</a>
<a href="http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy" target="_blank">http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy</a>
</pre>
</blockquote>
<br>
</div></div></div>
<br>_______________________________________________<br>
Uylug-programming mailing list<br>
<a href="mailto:Uylug-programming@listas.uylug.org.uy">Uylug-programming@listas.uylug.org.uy</a><br>
<a href="http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy" target="_blank">http://listas.uylug.org.uy/listinfo.cgi/uylug-programming-uylug.org.uy</a><br>
<br></blockquote></div><br></div>