Usuário com melhor resposta
Agrupar Intervalo de Horas

Pergunta
-
Boa Tarde Pessoal,
Estou precisando URGENTE de uma ajuda.
Tenho a seguinte tabela:
+------------+-----------+-----------+-----------+
| DATA | HORAS | NOME | EMPRESA |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 00:00 | jefferson | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 00:15 | jefferson | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 00:30 | jefferson | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 00:45 | jefferson | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 01:00 | jefferson | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 04:00 | jefferson | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 04:15 | jefferson | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 04:30 | jefferson | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 04:45 | jefferson | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 00:00 | bonamim | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 00:15 | bonamim | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 00:30 | bonamim | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 00:45 | bonamim | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 01:00 | bonamim | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 04:00 | bonamim | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 04:15 | bonamim | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 04:30 | bonamim | teste |
+------------+-----------+-----------+-----------+
| 11-09-2012 | 04:45 | bonamim | teste |
+------------+-----------+-----------+-----------+
Como dá para notar existe um intervalo entre os horários de 15 minutos, porém existe horários inexistentes entre a 01:00 e as 04:00, mas mesmo assim fica um relatório muito grande de apresentar.
Estou precisando quebrar este relatório no seguinte:
+------------+-------------------+-----------+-----------+
| DATA | HORAS | NOME | EMPRESA |
+------------+-------------------+-----------+-----------+
| 11-09-2012 | 00:00 - 01:00 | jefferson | teste |
+------------+-------------------+-----------+-----------+
| 11-09-2012 | 04:00 - 04:45 | jefferson | teste |
+------------+-------------------+-----------+-----------+
| 11-09-2012 | 00:00 - 01:00 | bonamim | teste |
+------------+-------------------+-----------+-----------+
| 11-09-2012 | 04:00 - 04:45 | bonamim | teste |
+------------+-------------------+-----------+-----------+
Alguém poderia me orientar?
Muito Obrigado!- Editado Jefferson Bonamim terça-feira, 11 de setembro de 2012 21:16 Estrutura
Respostas
-
Acredito que tenha conseguido uma boa solução para seu caso, utilizando CTE. Segue:
Create Table Teste (DATA Date, HORA Time, NOME VarChar(20), EMPRESA VarChar(20)) Insert Into Teste Values ('09-11-2012','00:00','jefferson','teste'), ('09-11-2012','00:15','jefferson','teste'), ('09-11-2012','00:30','jefferson','teste'), ('09-11-2012','00:45','jefferson','teste'), ('09-11-2012','01:00','jefferson','teste'), ('09-11-2012','04:00','jefferson','teste'), ('09-11-2012','04:15','jefferson','teste'), ('09-11-2012','04:30','jefferson','teste'), ('09-11-2012','04:45','jefferson','teste'), ('09-11-2012','00:00','bonamim', 'teste'), ('09-11-2012','00:15','bonamim', 'teste'), ('09-11-2012','00:30','bonamim', 'teste'), ('09-11-2012','00:45','bonamim', 'teste'), ('09-11-2012','01:00','bonamim', 'teste'), ('09-11-2012','04:00','bonamim', 'teste'), ('09-11-2012','04:15','bonamim', 'teste'), ('09-11-2012','04:30','bonamim', 'teste') ;With CTE1 as ( Select IdLinha = ROW_NUMBER() Over (Order by T.DATA, T.NOME, T.EMPRESA), T.* From Teste T Where (not Exists(Select * From Teste T2 Where (T2.DATA = T.DATA) and (T2.NOME = T.NOME) and (T2.EMPRESA = T.EMPRESA) and (T2.HORA = DATEADD(MINUTE, -15, T.HORA)))) ), CTE2 as ( Select IdLinha = ROW_NUMBER() Over (Order by T.DATA, T.NOME, T.EMPRESA), T.* From Teste T Where (not Exists(Select * From Teste T2 Where (T2.DATA = T.DATA) and (T2.NOME = T.NOME) and (T2.EMPRESA = T.EMPRESA) and (T2.HORA = DATEADD(MINUTE, 15, T.HORA)))) ) Select C1.DATA, HORA_INICIAL = C1.HORA, HORA_FINAL = C2.HORA, C1.NOME, C1.EMPRESA From CTE1 C1 Join CTE2 C2 on (C1.IdLinha = C2.IdLinha) Order by C1.DATA, C1.NOME, C1.EMPRESA
Roberson Ferreira - Database Developer
Acesse: www.robersonferreira.com.br
Email: contato@robersonferreira.com.brSe esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.- Marcado como Resposta Jefferson Bonamim quarta-feira, 12 de setembro de 2012 20:22
-
Segue sugestão:
--TABELA TEMPORÁRIA DECLARE @Teste Table (DATA Date, HORA Time, NOME VarChar(20), EMPRESA VarChar(20)) --POPULANDO A TABELA SET NOCOUNT ON Insert Into @Teste Values ('09-11-2012','00:00','jefferson','teste'), ('09-11-2012','00:15','jefferson','teste'), ('09-11-2012','00:30','jefferson','teste'), ('09-11-2012','00:45','jefferson','teste'), ('09-11-2012','01:00','jefferson','teste'), ('09-11-2012','04:00','jefferson','teste'), ('09-11-2012','04:15','jefferson','teste'), ('09-11-2012','04:30','jefferson','teste'), ('09-11-2012','04:45','jefferson','teste'), ('09-11-2012','00:00','bonamim', 'teste'), ('09-11-2012','00:15','bonamim', 'teste'), ('09-11-2012','00:30','bonamim', 'teste'), ('09-11-2012','00:45','bonamim', 'teste'), ('09-11-2012','01:00','bonamim', 'teste'), ('09-11-2012','04:00','bonamim', 'teste'), ('09-11-2012','04:15','bonamim', 'teste'), ('09-11-2012','04:30','bonamim', 'teste') SET NOCOUNT OFF -- SELECT FINAL SELECT DATA, CONVERT(CHAR(5),MIN(HORA)) + ' - ' + CONVERT(CHAR(5),MAX(HORA)) AS HORAS, NOME, EMPRESA FROM ( SELECT DATA, HORA ,CASE WHEN EXISTS(SELECT 1 FROM @Teste AS B WHERE B.NOME=A.NOME AND B.EMPRESA=A.EMPRESA AND DATEPART(HH,B.HORA)=(1+DATEPART(HH,A.HORA))) THEN DATEPART(HH,HORA)+1 ELSE DATEPART(HH,HORA) END AS H ,NOME, EMPRESA FROM @Teste AS A ) AS X GROUP BY DATA, NOME, EMPRESA, H ORDER BY NOME DESC, H --RESULTADO: /* (4 linha(s) afetadas) DATA HORAS NOME EMPRESA 2012-09-11 00:00 - 01:00 jefferson teste 2012-09-11 04:00 - 04:45 jefferson teste 2012-09-11 00:00 - 01:00 bonamim teste 2012-09-11 04:00 - 04:30 bonamim teste */
Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
- Marcado como Resposta Jefferson Bonamim quinta-feira, 13 de setembro de 2012 04:05
Todas as Respostas
-
Boa noite JBonamin, acho que a melhar opção é voce fazer um case com o range de horas que voce quer, por exemplo:
select distinct Data, horas = case when horas between '00:00' and '01:00' then '00:00 - 01:00' when horas between '04:00' and '04:45' then '04:00 - 04:45' end, Nome, Empresa from Tabela
Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.
- Sugerido como Resposta Alexandre Matayosi terça-feira, 11 de setembro de 2012 21:39
- Não Sugerido como Resposta Roberson Ferreira _Moderator terça-feira, 11 de setembro de 2012 22:19
-
Boa Noite Alexandre Matayosi,
Primeiramente muito obrigado pela atenção.
Então a sua sugestão é válida, porém não posso colocar fixo [between '00:00' and '01:00] ou [between '04:00' and '04:45], pois estes horários podem variar, ou seja, pode começar com 18:00 ao em vez de começar com 00:00 e terminar com 23:45 ao em vez de terminar em 04:45.
Por favor, preciso muito dessa ajuda, se eu não consegui explicar, por favor, me avisa.
Muito Obrigado!!!
JBonamim
-
Ok, então seria para pegar o periodo minimo e maximo por dia, pessoa e empresa, certo ? tente isto :
select Data, Min(Horas) + '-' + MAX(horas) as Horas, Nome, Empresa from tabela group by Data, Nome, Empresa
Acredito que va funcionar, avise se deu certo.Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.
-
Boa Noite Alexandre Matayosi,
Desculpa, mas não seria este o resultado, pois se eu realizar esta consulta vou ter o seguinte resultado:
11-09-2012 00:00-04:45 jefferson teste
Porém tenho que respeitar o intervalo de 15 minutos, ou seja, deveria de mostrar assim:
11-09-2012 00:00-01:00 jefferson teste
11-09-2012 04:00-04:45 jefferson teste
Pois do horário 01:00 até o horário 04:00 não tem os seguintes horários:
01:15
01:30
01:45
02:00
e assim por diante até chegar em 3:45.
Tenho que respeitar este intervalo de 15 minutos.
No começo quando pensei em solucionar este problema, pensei igual no que você acabou de passar.
Por favor, se eu não consegui explicar, me avisa, pois preciso de uma solução.
Muito Obrigado!!!
JBonamim
-
JBonamim, não vou conseguir fazer um script para te ajudar agora por que ja estou de saida, mas para fazer o que voce quer pensei em criar um looping ou cursor que vai ler registro a registro da sua tabela ordenado pelo horario, inicia pegando o menor horario e vai lendo linha a linha enquanto a diferença for igual a 15 minutos e colocaria em uma tabela temporaria o menor e maior horario para cada agrupamento, com isso no final voce teria uma tabela temporaria como voce quer, se não conseguir fazer isto me avisa que amanhã crio este looping.
Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.
-
Boa Noite Alexandre Matayosi,
Muito obrigado pela sua paciência.
Vou tentar fazer algo aqui, pois não conheço muito de 'Cursores'. O que eu conseguir eu posto aqui.
Se eu não conseguir e você puder me ajudar eu lhe agradeço.
Muito Obrigado, novamente.
JBonamim
-
Qual a versão do seu SQL?
Roberson Ferreira - Database Developer
Acesse: www.robersonferreira.com.br
Email: contato@robersonferreira.com.brSe esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta. -
-
Acredito que tenha conseguido uma boa solução para seu caso, utilizando CTE. Segue:
Create Table Teste (DATA Date, HORA Time, NOME VarChar(20), EMPRESA VarChar(20)) Insert Into Teste Values ('09-11-2012','00:00','jefferson','teste'), ('09-11-2012','00:15','jefferson','teste'), ('09-11-2012','00:30','jefferson','teste'), ('09-11-2012','00:45','jefferson','teste'), ('09-11-2012','01:00','jefferson','teste'), ('09-11-2012','04:00','jefferson','teste'), ('09-11-2012','04:15','jefferson','teste'), ('09-11-2012','04:30','jefferson','teste'), ('09-11-2012','04:45','jefferson','teste'), ('09-11-2012','00:00','bonamim', 'teste'), ('09-11-2012','00:15','bonamim', 'teste'), ('09-11-2012','00:30','bonamim', 'teste'), ('09-11-2012','00:45','bonamim', 'teste'), ('09-11-2012','01:00','bonamim', 'teste'), ('09-11-2012','04:00','bonamim', 'teste'), ('09-11-2012','04:15','bonamim', 'teste'), ('09-11-2012','04:30','bonamim', 'teste') ;With CTE1 as ( Select IdLinha = ROW_NUMBER() Over (Order by T.DATA, T.NOME, T.EMPRESA), T.* From Teste T Where (not Exists(Select * From Teste T2 Where (T2.DATA = T.DATA) and (T2.NOME = T.NOME) and (T2.EMPRESA = T.EMPRESA) and (T2.HORA = DATEADD(MINUTE, -15, T.HORA)))) ), CTE2 as ( Select IdLinha = ROW_NUMBER() Over (Order by T.DATA, T.NOME, T.EMPRESA), T.* From Teste T Where (not Exists(Select * From Teste T2 Where (T2.DATA = T.DATA) and (T2.NOME = T.NOME) and (T2.EMPRESA = T.EMPRESA) and (T2.HORA = DATEADD(MINUTE, 15, T.HORA)))) ) Select C1.DATA, HORA_INICIAL = C1.HORA, HORA_FINAL = C2.HORA, C1.NOME, C1.EMPRESA From CTE1 C1 Join CTE2 C2 on (C1.IdLinha = C2.IdLinha) Order by C1.DATA, C1.NOME, C1.EMPRESA
Roberson Ferreira - Database Developer
Acesse: www.robersonferreira.com.br
Email: contato@robersonferreira.com.brSe esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.- Marcado como Resposta Jefferson Bonamim quarta-feira, 12 de setembro de 2012 20:22
-
Segue sugestão:
--TABELA TEMPORÁRIA DECLARE @Teste Table (DATA Date, HORA Time, NOME VarChar(20), EMPRESA VarChar(20)) --POPULANDO A TABELA SET NOCOUNT ON Insert Into @Teste Values ('09-11-2012','00:00','jefferson','teste'), ('09-11-2012','00:15','jefferson','teste'), ('09-11-2012','00:30','jefferson','teste'), ('09-11-2012','00:45','jefferson','teste'), ('09-11-2012','01:00','jefferson','teste'), ('09-11-2012','04:00','jefferson','teste'), ('09-11-2012','04:15','jefferson','teste'), ('09-11-2012','04:30','jefferson','teste'), ('09-11-2012','04:45','jefferson','teste'), ('09-11-2012','00:00','bonamim', 'teste'), ('09-11-2012','00:15','bonamim', 'teste'), ('09-11-2012','00:30','bonamim', 'teste'), ('09-11-2012','00:45','bonamim', 'teste'), ('09-11-2012','01:00','bonamim', 'teste'), ('09-11-2012','04:00','bonamim', 'teste'), ('09-11-2012','04:15','bonamim', 'teste'), ('09-11-2012','04:30','bonamim', 'teste') SET NOCOUNT OFF -- SELECT FINAL SELECT DATA, CONVERT(CHAR(5),MIN(HORA)) + ' - ' + CONVERT(CHAR(5),MAX(HORA)) AS HORAS, NOME, EMPRESA FROM ( SELECT DATA, HORA ,CASE WHEN EXISTS(SELECT 1 FROM @Teste AS B WHERE B.NOME=A.NOME AND B.EMPRESA=A.EMPRESA AND DATEPART(HH,B.HORA)=(1+DATEPART(HH,A.HORA))) THEN DATEPART(HH,HORA)+1 ELSE DATEPART(HH,HORA) END AS H ,NOME, EMPRESA FROM @Teste AS A ) AS X GROUP BY DATA, NOME, EMPRESA, H ORDER BY NOME DESC, H --RESULTADO: /* (4 linha(s) afetadas) DATA HORAS NOME EMPRESA 2012-09-11 00:00 - 01:00 jefferson teste 2012-09-11 04:00 - 04:45 jefferson teste 2012-09-11 00:00 - 01:00 bonamim teste 2012-09-11 04:00 - 04:30 bonamim teste */
Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
- Marcado como Resposta Jefferson Bonamim quinta-feira, 13 de setembro de 2012 04:05
-
Jefferson, conseguiu?
Roberson Ferreira - Database Developer
Acesse: www.robersonferreira.com.br
Email: contato@robersonferreira.com.brSe esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta. -
-
Boa Tarde Leonardo Marcelino,
Desculpa a demora, mas ainda não testei o seu código. Hoje a noite a partir das 23:30 estarei testando e estarei colocando como 'útil' ou 'resposta' o que você me passou.
Mas desde já eu agradeço o apoio e a atenção.
Muito Obrigado!
JBonamim
-
Boa Tarde Roberson Ferreira,
Enviei um e-mail para: contato@robersonferreira.com.br, com algumas dúvidas, o assunto do e-mail é o mesmo deste tópico ("Agrupar Intervalo de Horas"), meu e-mail é: supersbonamim@hotmail.com.
Fico agradecido se você puder dar uma olhada.
Muito Obrigado!
JBonamim
-
Boa Noite Leonardo Marcelino,
Muito obrigado pela atenção. A sua solução também foi ótima.
Você poderia me orientar o que foi feito passo a passo? Sou "leigo" ainda em SQL.
Meu e-mail: bonamim.jb@gmail.com ou supersbonamim@hotmail.com
Muito Obrigado, novamente.
JBonamim