Jump to content
JoaoVM

[Resolvido] Query com registos de duas linhas especificas

Recommended Posts

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?

Share this post


Link to post
Share on other 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.

Edited by 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."

Share this post


Link to post
Share on other 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());

Edited by JoaoVM

Share this post


Link to post
Share on other 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

Share this post


Link to post
Share on other 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

Edited by JoaoVM

Share this post


Link to post
Share on other 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."

Share this post


Link to post
Share on other 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;

Share this post


Link to post
Share on other 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

Share this post


Link to post
Share on other 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

  • Vote 1

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

Share this post


Link to post
Share on other 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.)

Share this post


Link to post
Share on other 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

  • Vote 1

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

Share this post


Link to post
Share on other 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.

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.