Agrupar Intervalo de Horas

Question

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!

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

Qual a versão do seu SQL?

---

Roberson Ferreira - Database Developer
Acesse: www.robersonferreira.com.br
Email: [e-mail]

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.

Answer 8

Bom Dia, A versão do SQL que utilizo é o Server 2008. Caso precise de mais alguma informação, por favor, pode perguntar. Muito Obrigado!!!!

---

JBonamim

Answer 9

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: [e-mail]

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.

Answer 10

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

Answer 11

Jefferson, conseguiu?

---

Roberson Ferreira - Database Developer
Acesse: www.robersonferreira.com.br
Email: [e-mail]

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.

Answer 12

Boa Tarde Roberson Ferreira,

Muito obrigado mesmo. Desculpa não responder antes, pois estava ocupado.

Ficou excelente para o que eu estava precisando.

Muito Obrigado!

---

JBonamim

Answer 13

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

Answer 14

Boa Tarde Roberson Ferreira,

Enviei um e-mail para: [e-mail], com algumas dúvidas, o assunto do e-mail é o mesmo deste tópico ("Agrupar Intervalo de Horas"), meu e-mail é: [e-mail].

Fico agradecido se você puder dar uma olhada.

Muito Obrigado!

---

JBonamim

Answer 15

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: [e-mail] ou [e-mail]

Muito Obrigado, novamente.

---

JBonamim