Ir para o conteúdo
  • Revista PROGRAMAR: Já está disponível a edição #60 da revista programar. Faz já o download aqui!

JoaoVM

[Resolvido] Soma de uma coluna

Mensagens Recomendadas

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:

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...

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros 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

Editado por JoaoVM

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros 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).

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros 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.

Editado por JoaoVM

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros 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

Editado por JoaoVM

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros 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.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros 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).

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros 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.

Editado por JoaoVM

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros 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.

  • Voto 1

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros 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:

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.

Editado por JoaoVM

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites

Crie uma conta ou ligue-se para comentar

Só membros podem comentar

Criar nova conta

Registe para ter uma conta na nossa comunidade. É fácil!

Registar nova conta

Entra

Já tem conta? Inicie sessão aqui.

Entrar Agora

×

Aviso Sobre Cookies

Ao usar este site você aceita os nossos Termos de Uso e Política de Privacidade. Este site usa cookies para disponibilizar funcionalidades personalizadas. Para mais informações visite esta página.