Jump to content
JoaoVM

[Resolvido] Soma de uma coluna

Recommended Posts

JoaoVM

Boas, tenho um problema parecido...

Tenho este código:

SELECT E.data AS data_entrada, E.hora_entrada, S.data AS data_saida,S.hora_saida,
  MIN(TIMEDIFF(CONCAT(S.data,' ',S.hora_saida), CONCAT(E.data,' ',E.hora_entrada))) AS DIFF
  FROM transacoes_bmw AS E, transacoes_bmw AS S
  WHERE E.rfid = 1234567890
   AND S.rfid = E.rfid
   AND E.linha = 126
   AND S.linha = E.linha
   AND E.evento = 'E' 
   AND S.evento = 'S' 
   AND CONCAT(S.data, ' ', S.hora_saida) >= CONCAT(E.data, ' ', E.hora_entrada)
   AND E.data = timestamp(current_date())
  GROUP BY E.hora_entrada
Que me devolve:

http://img13.imageshack.us/img13/296/lpsk.png

O que quero é fazer a soma de todos os valores da coluna diff.

Não é possível fazer a soma dos valores em mysql certo?visto que já é um AS...

Tenho algo do género:

$sql="QUERY IGUAL A CIMA";
$executa=mysql_query($sql);
$somar=0;
while($resultado=mysql_fetch_array($executa)){

   $somar += $resultado['DIFF'];
}
echo $somar;
Ele não me soma minutos, já tentei com a função date() e format() mas o resultado não é correto...

Share this post


Link to post
Share on other sites
JoaoVM

Sim mas não posso fazer um SUM do ALIAS,

Está errado, diz-me que o uso da função GROUP está errada, podes ajudar-me a perceber como fica a query?

Não pode ser apenas colocar SUM nem posso fazer SUM(DIFF)...

SELECT E.data AS data_entrada, E.hora_entrada, S.data AS data_saida,S.hora_saida,
  SUM(MIN(TIMEDIFF(CONCAT(S.data,' ',S.hora_saida), CONCAT(E.data,' ',E.hora_entrada)))) AS DIFF
  FROM transacoes_bmw AS E, transacoes_bmw AS S
  WHERE E.rfid = 1234567890
	   AND S.rfid = E.rfid
	   AND E.linha = 126
	   AND S.linha = E.linha
	   AND E.evento = 'E'
	   AND S.evento = 'S'
	   AND CONCAT(S.data, ' ', S.hora_saida) >= CONCAT(E.data, ' ', E.hora_entrada)
	   AND E.data = timestamp(current_date())
  GROUP BY E.hora_entrada

Edited by JoaoVM

Share this post


Link to post
Share on other sites
Rui Carlos

Em último caso, podes usar queries aninhadas.

Adicionalmente, não faz sentido querer a soma de tudo em conjunto com atributos como o data_entrada, pelo que a tua query devia obter apenas a soma.

No entanto, ainda não percebi se aquele MIN era mesmo necessário (não parece muito lógico agrupar registos de dias diferentes com a mesma hora de entrada, mas não sei o que estás a tentar fazer).

Share this post


Link to post
Share on other sites
JoaoVM

Posso fazer desta forma o resultado é o mesmo:

CONVERT(TIMEDIFF(CONCAT(S.data,' ',S.hora_saida), CONCAT(E.data,' ',E.hora_entrada)), TIME) AS DIFF

No entanto, ainda não percebi se aquele MIN era mesmo necessário (não parece muito lógico agrupar registos de dias diferentes com a mesma hora de entrada, mas não sei o que estás a tentar fazer).

Quanto a isso esta query foi recomendada pelo HappyHippyHippo, e está a funcionar perfeitamente que é dar-me os registos das diferenças entre as entradas e saídas, basicamente apenas existem registos de dias diferentes quando um funcionário faz turnos que entra as 22h de um dia e sai as 6h do dia seguinte, mas este ainda não é o cenário visto que estou agora a fazer para o caso geral.

Edited by JoaoVM

Share this post


Link to post
Share on other sites
JoaoVM

Rui Carlos e a solução em PHP?Será mais fácil?não estou a conseguir chegar a query, depois de tirar o MIN e colocar o CONVERT

CONVERT((SUM(TIMEDIFF(CONCAT(S.data,' ',S.hora_saida), CONCAT(E.data,' ',E.hora_entrada)))), TIME) AS DIFF

Obtenho um resultado estúpido...

Já agora se retirar o MIN o resultado é o mesmo que com MIN

Edited by JoaoVM

Share this post


Link to post
Share on other sites
Rui Carlos

Penso que no PHP vais ter um problema semelhante, de lidar correctamente com valores que representam tempo.

Aqui sugerem a conversão para segundos antes de se somarem as diferenças. Podes experimentar esta solução.

Acho a query base é um bocado estranha, principalmente quando continua a funcionar depois de retirares o MIN.

Share this post


Link to post
Share on other sites
Rui Carlos

O esquema da BD foi desenvolvido por ti, ou foi-te impostos?

É que o esquema não faz muito sentido, e vai complicar as queries que precisas de usar (provavelmente sem necessidade).

Tem em atenção que não vais poder retirar o MIN da query inicial (pelo menos com a informação que deste, não é garantido que continue a funcionar). Para a soma, em último caso usa essa query como sub-query. Sem saber exactamente o problema que estás a ter, é difícil dar mais sugestões (até porque os erros com datas/tempos costumam estar em detalhes pouco óbvios).

Share this post


Link to post
Share on other sites
JoaoVM

Rui Carlos o esquema da BD foi desenvolvido por mim. Quando desenvolvo um esquema de BD penso no que preciso, claro que posso alterar.

Eu vou tentar explicar o que pretendo com tudo isto.

Tenho uma aplicação para fazer o registo de entradas e saídas de uma linha de produção, o funcionário passa o cartão para se autenticar e tem a data e hora da sua entrada e é inserido um evento de entrada, quando volta a passar o cartão é inserido um evento de saída com a data e hora de saída.

Estrutura de entrada/saida da tabela transacoes_bmw:

Explicar também que os p1, p2,...,p16 são os períodos de trabalho em horas que vai dar com o calculo do p1 da entrada e o p1 da saída o tempo útil de trabalho do funcionário nesse período.

| id_transacao | num_colab | rfid | pnome | unome | data | hora | hora_entrada | hora_saida | evento | p1 | p2 | ... | p16 | total_horas |

123 | 123456 | 1234567890| joao | martins| 2013-11-12 | 20:10:00 | 20:10:00 | 00:00:00 | E | 00:50:00 | 01:00:00 | ... |01:00:00 | 00:00:00|

124 | 123456 | 1234567890| joao | martins| 2013-11-12 | 20:30:00 | 00:00:00 | 20:30:00 | S | -00:30:00 | -01:00:00 | ... | -01:00:00 | 00:20:00|

Por exemplo o calculo do P1:

Entro as 20:10:00 (Falta 50 minutos para o período seguinte)

Saio as 20:30:00 (Falta 30 minutos para o período seguinte)

Então faço (00:50:00)-(00:30:00) = 00:20:00

Ou seja o tempo útil de trabalho naquele período é de 20 minutos.

No final somar o tempo útil de todos os funcionários no P1.

Para os períodos seguinte a mesma coisa tendo em conta que o tempo útil é calculado hora a hora para fazer o calculo do PPH(Produção por Pessoa por Hora).

O que pretendo é quando faço a hora de saída que me devolva o tempo total que o colaborador esteve a trabalhar, e no final dos 16 períodos, ou melhor 8 períodos porque os 16 períodos correspondem a 2 turnos de trabalho, ou seja, no final dos 8 períodos que me me some todas as total horas do funcionário.

Essa quero devolve-me a diferença de tempo entre a entrada e a saída, o que pretendo é apenas somar as diferenças de tempo para um determinado funcionário. Também fazer o calculo do tempo útil em cada período.

Rui Carlos, tentei explicar o melhor possível, se tiveres alguma duvida a perceber o meu problema avisa que não tenho problema nenhum em explicar de outra forma, ou de forma mais clara.

Dai pensar que o meu esquema da BD não está tão mal quanto isso mas claro, aceito criticas, construtivas de preferencia ;)

E peço desculpa pelo tamanho do tópico.

Edited by JoaoVM

Share this post


Link to post
Share on other sites
Rui Carlos

Uma das coisas que não faz sentido é teres hora_entrada e hora_saida, quando uma delas parece estar sempre vazia e a outra parece ser igual à coluna hora (ou seja, bastava esta última coluna, e a coluna evento dizia-te se era hora de entrada ou saída).

Adicionalmente não sei se não seria melhor teres uma tabela para entradas, e outra para saídas. Também era capaz de ser útil associares uma saída a uma entrada, para facilitares o cálculo dos intervalos. Mas isto são opções mais discutíveis.

Depois fico com a ideia que os campos P1, ... deviam estar numa tabela separada, pois deste a entender que se referem a períodos e não ao registo das entradas/saídas. Tens outros problemas de normalização, pois assumo que o num_colab é suficiente para identificar o colaborador, logo os restantes campos do colaborador não deviam estar nessa tabela.

É verdade que estas coisas da normalização são conceitos genéricos, e que há situações específicas onde é conveniente quebrar algumas das regras. Mas da tua descrição não vi razões para isso acontecer.

Dito isto, deves conseguir fazer o que queres com esse esquema, e se achas que consegues fazer as queries que precisas assim (e não ligues aos problemas com as somas de tempos, que é normal dar problemas), provavelmente deves manter a estrutura. Mas cuidado com as queries que usas, pois como já referi, há algumas alterações que parecem inofensivas, mas não o são.

Também não sei de onde veio este problema. A verdade é que ele não parece propriamente simples, mesmo para quem já tem alguma experiência com BDs.

  • Vote 1

Share this post


Link to post
Share on other sites
JoaoVM

Rui Carlos, consegui resolver o problema, a solução que encontrei foi criar uma vista e fazer a soma de elementos da vista, ou seja, não conseguia fazer a soma da coluna diff porque era um AS e não o conseguia fazer, mas sendo a coluna diff uma nova coluna que não um AS na minha vista consigo fazer a soma e obter o resultado pretendido, apenas passei os dados necessários e consegui somar os tempos corretamente.

Depois de criar a vista com o seguinte resultado:

http://imageshack.us/a/img13/296/lpsk.png

Utilizei o código para fazer a soma da coluna

SELECT rfid, data_entrada, hora_entrada, data_saida, hora_saida,
SEC_TO_TIME(SUM(TIME_TO_SEC(DIFF))) AS TOTAL_HORAS,
(SUM(TIME_TO_SEC(DIFF))) AS TOTAL_SEGUNDOS
FROM DIFF
WHERE DATA_ENTRADA=timestamp(CURRENT_DATE());
Depois disto passei o total de horas para segundos também presente na consulta e devolvi para o php. Depois de estar no php dividi por 60 para me dar o numero de minutos em inteiro e pronto. Problema Resolvido ;)

Sei que a minha estrutura não é das melhores, sei que tem problemas de normalização também, e que posso estar a não fazer as coisas da melhor maneira mas para já vou manter a estrutura, não querendo dizer que mais tarde não a mude...desde já obrigado Rui Carlos pela colaboração.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.