Ir para o conteúdo
JoaoVM

[Resolvido] Query com registos de duas linhas especificas

Mensagens Recomendadas

JoaoVM

Boas Pessoal,

Tenho uma duvida no que trata a uma consulta em MYSQL que tem 2 campos, penso que tenho que fazer com sub-consultas mas não estou a conseguir, dai pedir a vossa ajuda.

Esta é a minha tabela onde quero sacar os dados, http://prntscr.com/217288.

O que quero é a diferença de tempo entre a entrada do Colaborador e a saída do Colaborador, cada registo é um evento de E=entrada e S=saída.

Para isso tentei algo do género:

SELECT timediff('hora_saida', 'hora_entrada') as Total from transacoes_bmw where rfid=123456789 and linha=126 and evento='E' and evento='S';

Mas não consigo saber através desta minha query de onde vem o hora_entrada, se do evento de E ou S.

Penso que o problema se resolve com sub-consultas para encontrar o registo de cada uma, mas não estou a ver como fazer...

Alguém me ajuda?

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
pikax

maneira que nao e' ideal:

SELECT DATEDIFF(HOUR,r.Data,
	   (SELECT re.Data FROM registoentradas re WHERE re.Data>r.Data LIMIT 1)
						 )
FROM registoentradas r -- WHERE ........

'a formas de se fazer melhor, so' que a forma mais directa de se fazer.

Editado por pikax

Por muito mais que que estude só aprendo uma coisa, que ainda tenho muita coisa para aprender.

A beleza de um código está em decompor problemas complexos em pequenos blocos simples.

"learn how to do it manually first, then use the wizzy tool to save time."

"Kill the baby, don't be afraid of starting all over again. Fail soon, learn fast."

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
JoaoVM

Não era bem isso que pretendia pikax, esqueci-me de dizer que a data era a data atual, o que quero é a diferença de tempo em minutos não a data, so que quero a hora de saída de um registo e a hora de entrada de outro registo. não sei se me fiz entender, se quiseres explico mais detalhado..

SELECT timediff('hora_saida', 'hora_entrada') AS Total
FROM transacoes_bmw
WHERE rfid=123456789 AND
linha=126 AND
evento='E'AND
evento='S' AND
data=timestamp(current_date());

Editado por JoaoVM

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
HappyHippyHippo

tens a noção que os dados apresentados na imagem não fazem sentido ...

olha para os tempos de entrada e de saida ..

14:34:49 - entrada

14:36:30 - entrada ??? mas já não tinha entrado ???

14:37:25 - saída

14:43:50 - saída ??? novamente ???


IRC : sim, é algo que ainda existe >> #p@p

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
JoaoVM

Peço desculpa se a imagem não tem sentido, é só do Colaborador Joao Martins, o que está em branco apaguei porque são dados confidenciais...devia ter explicado isso

Editado por JoaoVM

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
pikax
Não era bem isso que pretendia pikax, esqueci-me de dizer que a data era a data atual, o que quero é a diferença de tempo em minutos não a data, so que quero a hora de saída de um registo e a hora de entrada de outro registo. não sei se me fiz entender, se quiseres explico mais detalhado..

O que eu fiz foi um mero exemplo, tens que adaptar ao teu codigo, para mais nem meti where no sub-select.


Por muito mais que que estude só aprendo uma coisa, que ainda tenho muita coisa para aprender.

A beleza de um código está em decompor problemas complexos em pequenos blocos simples.

"learn how to do it manually first, then use the wizzy tool to save time."

"Kill the baby, don't be afraid of starting all over again. Fail soon, learn fast."

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
JoaoVM

Obrigado pelas dicas pikax, ficou assim....

SELECT TIMEDIFF(hora_saida, hora_entrada) AS TOTAL
FROM
(SELECT hora_saida FROM transacoes_bmw 
WHERE          
rfid='1234567890' AND
linha='126' AND
evento='S'AND
DATA=TIMESTAMP(CURRENT_DATE())) SAIDA,

(SELECT hora_entrada FROM transacoes_bmw 
WHERE          
rfid='1234567890' AND
linha='126' AND
evento='E'AND
DATA=TIMESTAMP(CURRENT_DATE())) ENTRADA;

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
HappyHippyHippo

sem subqueries :

 SELECT E.data AS data_entrada,                                                                    -- data de entrada
        E.hora_entrada,                                                                            -- hora de entrada
        S.data AS data_saida,                                                                      -- data de saída
        S.hora_saida,                                                                              -- hora de saída
        MIN(TIMEDIFF(CONCAT(S.data,' ',S.hora_saida), CONCAT(E.data,' ',E.hora_entrada))) AS diff  -- diferença
   FROM transacoes_bmw AS E,                                                                       -- tabela entradas
        transacoes_bmw AS S                                                                        -- tabela saídas
  WHERE E.rfid = 123456789                                                                         -- filtro de funcionário para de entradas
    AND S.rfid = E.rfid                                                                            -- filtro de funcionário para de saídas
    AND E.linha = 126                                                                              -- filtro da linha para de entradas
    AND S.linha = E.linha                                                                          -- filtro da linha para de saídas
    AND E.evento = 'E'                                                                             -- filtro to tipo de evento da tabela de entradas
    AND S.evento = 'S'                                                                             -- filtro to tipo de evento da tabela de saídas
    AND CONCAT(S.data, ' ', S.hora_saida) >= CONCAT(E.data, ' ', E.hora_entrada)                   -- filtro para saída sempre maior que a da entrada
GROUP BY E.hora_entrada                                                                             -- agrupamento para a função MIN


IRC : sim, é algo que ainda existe >> #p@p

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
HappyHippyHippo

É sempre bom ter outra solução, Obrigado HappyHippyHippo ;)

não é só uma questão de mais soluções, é uma questão de performance.

tens uma query com duas subqueries ou outra que só falta uma optimização que seria (talvez) limitar a data de saída para menor que 1 dia após a data de entrada para minimizar o agrupamento e aplicação do MIN

além disso, existe casos que a query que apresentei é sensivel ao contrário da outra como combinações de entradas e saídas para dias diferentes, ex:

data/hora_entrada | data/hora_saida
--------------------+--------------------
2013-11-10 23:59:00 | 2013-11-11 00:01:00

  • Voto 1

IRC : sim, é algo que ainda existe >> #p@p

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
Rui Carlos

sem subqueries :

 SELECT E.data AS data_entrada,																	-- data de entrada
	 E.hora_entrada,																			-- hora de entrada
	 S.data AS data_saida,																	  -- data de saída
	 S.hora_saida,																			  -- hora de saída
	 MIN(TIMEDIFF(CONCAT(S.data,' ',S.hora_saida), CONCAT(E.data,' ',E.hora_entrada))) AS diff  -- diferença
FROM transacoes_bmw AS E,																	   -- tabela entradas
	 transacoes_bmw AS S																		-- tabela saídas
  WHERE E.rfid = 123456789																		 -- filtro de funcionário para de entradas
 AND S.rfid = E.rfid																			-- filtro de funcionário para de saídas
 AND E.linha = 126																			  -- filtro da linha para de entradas
 AND S.linha = E.linha																		  -- filtro da linha para de saídas
 AND E.evento = 'E'																			 -- filtro to tipo de evento da tabela de entradas
 AND S.evento = 'S'																			 -- filtro to tipo de evento da tabela de saídas
 AND CONCAT(S.data, ' ', S.hora_saida) >= CONCAT(E.data, ' ', E.hora_entrada)				   -- filtro para saída sempre maior que a da entrada
GROUP BY E.hora_entrada																			 -- agrupamento para a função MIN

HHH, podes explicar a necessidade do GROUP BY E.hora_entrada?

Imagino que esteja relacionado com o facto de haver entradas e saídas "repetidas", mas ainda assim não estou a ver a sua utilidade.

(Esta dúvida surgiu na sequência deste tópico.)

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
HappyHippyHippo

HHH, podes explicar a necessidade do GROUP BY E.hora_entrada?

Imagino que esteja relacionado com o facto de haver entradas e saídas "repetidas", mas ainda assim não estou a ver a sua utilidade.

(Esta dúvida surgiu na sequência deste tópico.)

dados exemplo:

   data    | hora_entrada | hora_saida | evento
2013-11-10 |   14:34:49   |  00:00:00  |   E
2013-11-10 |   00:00:00   |  14:36:49  |   S
2013-11-10 |   14:45:49   |  00:00:00  |   E
2013-11-10 |   00:00:00   |  14:45:59  |   S

(não perguntes o porque desta estrutura ... foi apresentada no primeiro post, e um gajo trabalha com o que tem ...)

dados que deveriam ser apresentados :

2013-11-10 14:34:49 --> 2013-11-10 14:36:49

2013-11-10 14:45:49 --> 2013-11-10 14:45:59

no entanto, sem o GROUP BY, o único registo que será apresentado será o segundo porque é o que tem a menor diferença entrada/saída

  • Voto 1

IRC : sim, é algo que ainda existe >> #p@p

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
Rui Carlos

A minha dúvida estava também relacionada com o MIN, que achava não ser necessário, e estava a pensar que o MIN era uma consequência do GROUP BY, mas afinal é ao contrário. O MIN é necessário para evitar que todas as combinações sejam mostradas, mas depois o GROUP BY também passa a fazer falta.

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.