Selecionar Horario com intervalo maior que 30 minutos

[Melrio 0]

Olá pessoal,
Preciso de uma ajuda de vcs, tenho que fazer uma procedure que recebe uma matricula de funcionário, e a data do dia, mostrar a entrada dele e a saida(isso consigo fazer com MIN E MAX) e também mostrar a hora de almoço. Só neste caso, foi decidido que toda intervalo de SAIDA com retorno maior que 30 minutos, é para ser mostrado. Esse é o problema, o funcionário pode sair e voltar varias vezes ao dia, como exemplo ir ao banco, fazer lanche e etc. Como posso pegar estes horários ?

[Motta 645]

Primeiro, como é a estrutura da tabela/dados ?

Segundo , veja se ajuda http://forum.imasters.com.br/topic/507601-comparar-registro-com-o-anterior-mysql/#entry2007457

[Melrio 0]

Primeiro, como é a estrutura da tabela/dados ?

Segundo , veja se ajuda http://forum.imasters.com.br/topic/507601-comparar-registro-com-o-anterior-mysql/#entry2007457

Olhei o lnk mas não ajudou.

 

 

Consigo criar um select com 4 tabelas, que me mostra,

 

Matricula | Nome | data | Tipo | Porta

------------|-------------|------------------------|----------|---------

123 | Carlos |05/06/2014 10:00 |Entrada| Staff

123 |Carlos |05/06/2014 11:20 | Saida | Staff

123 |Carlos |05/06/2014 11:40 |Entrada| Staff

123 |Carlos |05/06/201413:00 |Saida | Satff

123 |Carlos |05/06/201414:00 |Entrada| Staff

123 |Carlos |05/06/201415:3 |Saida | Staff

123 |Carlos |05/06/201416:10 |Entrada|Satff

123 |Carlos |05/06/201419:00 |Saida | Satff

 

precido exibir este resultado, em uma query(Já fiz), em outra query preciso exibir somente, hora da entrada, hora de saida(também consigo) e os horarios de entrada e saida que o intervalo for maior de 30 minutos, exemplo; mostraria,

 

Mat |Carlos | data

----------------------------------

123 | Carlos |13:00 á 14:00

123 | Carlos |15:30 á 16:10

[Motta 645]

Se você usar o select do primeiro resultado como

uma tabela virtual você faria um join dela com ela mesma unindo de forma articifial um registro ao seu posterior , 1 ao2, 3 ao 4 etc, o "tipo" poderia ser usado para isto.

http://forum.imasters.com.br/topic/515682-somar-horas/

 

http://forum.imasters.com.br/topic/479282-calculando-saldo-de-banco-de-horas/

 

 

Veja se ajuda.

[Melrio 0]

Se você usar o select do primeiro resultado como

uma tabela virtual você faria um join dela com ela mesma unindo de forma articifial um registro ao seu posterior , 1 ao2, 3 ao 4 etc, o "tipo" poderia ser usado para isto.

http://forum.imasters.com.br/topic/515682-somar-horas/

 

http://forum.imasters.com.br/topic/479282-calculando-saldo-de-banco-de-horas/

 

 

Veja se ajuda.

No caso vc diz que se eu fizer um insert, do select do primeiro resultado, em uma tabela temporária e nesta mesma temporária faço o join ? Só para ver se eu entendi.

O segundo link é mais parecido com o meu problema, mas no meu caso, eu posso ter várias entradas e saidas no mesmo dia. Vou fazer uma adaptação, e testar para ver o que dá.

 

 

[Motta 645]

Um problema pode estar no vigia da noite pois ele entra 5/6/14 e sai 6/6/14 ...

[advaldomesquita 93]

Um DATEDIFF em uma tabela temporaria com um identity - 1 nao resolve?

[Melrio 0]

Esse é o select, coloquei ele dentro de uma tabela temporária.

Matr| Nome  |  Data              | Tipo     | Acesso
_________________________________________________________
528 |Carlos |20/05/2014 09:39:00 | Entrada | RECEPÇÃO 
528 |Carlos |20/05/2014 13:01:43 | Saída | RECEPÇÃO 
528 |Carlos |20/05/2014 13:01:52 | Entrada | RECEPÇÃO 
528 |Carlos |20/05/2014 13:04:22 | Saída | RECEPÇÃO 
528 |Carlos |20/05/2014 14:11:50 | Entrada | RECEPÇÃO 
528 |Carlos |20/05/2014 18:50:51 | Saída | RECEPÇÃO

 

E esse aqui, foi o que fiz com a tabela temporária, mas ela está mostrando mais campos:

select ent.Matricula, ent.Data as entrada, sai.Data as saida 

from  #relatorioTemp ent,#relatorioTemp sai
where  ent.Matricula = sai.Matricula
and DATEDIFF(mi, ent.Data ,sai.Data)>30
and sai.Tipo ='Saída'
and ent.Tipo ='Entrada'

Matr| Entrada              | Saida
________________________________________________
​528 | 20/05/2014  09:39:00 | 20/05/2014 13:01:43
528 | 20/05/2014  09:39:00 | 20/05/2014 13:04:22
528 | 20/05/2014  09:39:00 | 20/05/2014 18:50:51
528 | 20/05/2014  13:01:52 | 20/05/2014 18:50:51
528 | 20/05/2014  14:11:50 | 20/05/2014 18:50:51

Antes de postar aqui, eu já havia revirado o google todo pesquisando sobre.

Tá osso...rsrsrsr

Um problema pode estar no vigia da noite pois ele entra 5/6/14 e sai 6/6/14 ...

Não há vigia, ou seja, só horário "normal".

[Motta 645]

Como disse o A.Jr coloque um sequencial na tabela temporária pois a comparação fica

Seq |Matr| Nome  |  Data              | Tipo     | Acesso
_________________________________________________________
001 528 |Carlos |20/05/2014 09:39:00 | Entrada | RECEPÇÃO 
002 528 |Carlos |20/05/2014 13:01:43 | Saída | RECEPÇÃO 
003 528 |Carlos |20/05/2014 13:01:52 | Entrada | RECEPÇÃO 
004 528 |Carlos |20/05/2014 13:04:22 | Saída | RECEPÇÃO 
005 528 |Carlos |20/05/2014 14:11:50 | Entrada | RECEPÇÃO 
006 528 |Carlos |20/05/2014 18:50:51 | Saída | RECEPÇÃO

select ent.Matricula, ent.Data as entrada, sai.Data as saida 

from  #relatorioTemp ent,#relatorioTemp sai
where  ent.Matricula = sai.Matricula
and ent.id = sai.id+1
and sai.Tipo ='Saída'
and ent.Tipo ='Entrada'

[Melrio 0]

Pow obrigadão ae cara, valeu mesmo. Não tinha visto a resposta do A.Jr. Vlw também

Mas encontrei um problema, estou tentando dar um left join para quando não "haver" horario de almoço, o campo ficar em branco. Mas não estou conseguindo.

 

select

ent.Matricula,

ent.Nome,

min(x.data) as PrimeiraHora,

convert(varchar,sai.Data,108) as saida_ausente_mais_30,

convert(varchar,ent.Data,108) as entrada__ausente_mais_30,

Max(x.data) as UltimaHora

from

#relatorioTemp x

left join #relatorioTemp ent on x.Matricula = ent.Matricula

left join #relatorioTemp sai on sai.Matricula = ent.Matricula

where --ent.Matricula = sai.Matricula

-- x.matricula = sai.matricula

-- sai.matricula = x.matricula

ent.Seq = sai.Seq+1

and sai.Tipo ='Saída'

and ent.Tipo ='Entrada'

and datediff(mi,convert(varchar,sai.Data,108),convert(varchar,ent.Data,108)) >= 30

and datediff(day,sai.Data,ent.Data) = 0

Tô pesquisando como fazer isso, pois haverá varias datas, não somente a do dia.
Se puderam me ajudar a entender.

[Motta 645]

Faça só um outer join "entrada" left join "saida".

[Melrio 0]

Faça só um outer join "entrada" left join "saida".

From

#relatorioTemp x

left outer join #relatorioTemp ent on x.Matricula = ent.Matricula

left outer join #relatorioTemp sai on ent.Matricula = sai.Matricula

 

Assim?

[Melrio 0]

Na verdade a duvida agora é, como que faço para pegar a primeira e ultima hora do dia?
Pensando aqui, e com min e max, não está indo, mesmo colocando datediff em ent.data e sai.data .

[Melrio 0]

Consegui fazendo desta forma:

Exibe os intervalos:

select

ent.Matricula,

ent.Nome,

sai.data as saida_ausente_mais_30,

ent.data as entrada_ausente_mais_30

from

#relatorioTemp ent ,#relatorioTemp sai

where ent.Matricula = sai.Matricula

and ent.Seq = sai.Seq+1

and sai.Tipo ='Saída'

and ent.Tipo ='Entrada'

and datediff(mi,sai.data,ent.data) >= 30

and datediff(day,sai.Data,ent.data) = 0

order by ent.matricula asc

exibe a primeira hora do dia e ultima de cada matricula

 

select matricula,nome, data as saida

from ( select *, row_number() over(partition by convert(varchar(8),data,112),matricula order by data desc) Corr

from #relatorioTemp where matricula in (select distinct matricula from #relatorioTemp)) A

where Corr = 1

select matricula,nome, data as entrada

from ( select *, row_number() over(partition by convert(varchar(8),data,112),matricula order by data asc) Corr

from #relatorioTemp where matricula in (select distinct matricula from #relatorioTemp)) A

where Corr = 1


mas como posso fazer para unir as três e gerar uma só?

[Motta 645]

Faça destas 3 queries tabelas virtuais e faça um union delas.

 

Ou

 

Faça das duas queries de min. e max. subqueries de coluna.

[Melrio 0]

Faça destas 3 queries tabelas virtuais e faça um union delas.

 

Ou

 

Faça das duas queries de min. e max. subqueries de coluna.

Pensei na segunda opção, mas que seria a subqueries é a de intervalo, pois ela pode ter coluna null.

[Motta 645]

Usando max e min sempre retorna valor , nem que seja NULL.

[Melrio 0]

Sim, mas neste caso o max e min, vão retornar sempre a primeira entrada e a ultima saida.
Já o intervalo pode haver null.

[advaldomesquita 93]

Tem um jeito mais facil:

 

http://gustavomaiaaguiar.wordpress.com/2009/04/03/como-realizar-calculos-com-horas-no-sql-server-%E2%80%93-parte-i/

 

[]´s

Ah, e este post tambem pode ser util

 

http://forum.imasters.com.br/topic/515682-somar-horas/

[Melrio 0]

Tem um jeito mais facil:

 

http://gustavomaiaaguiar.wordpress.com/2009/04/03/como-realizar-calculos-com-horas-no-sql-server-%E2%80%93-parte-i/

 

[]´s

Ah, e este post tambem pode ser util

 

http://forum.imasters.com.br/topic/515682-somar-horas/

Galera, já já posto como fiz aqui. Só para adiantar, usei o php para mostrar os resultados, pois achei mais fácil rsrsrsr..