none
Agrupar Intervalo de Horas RRS feed

  • 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!
    terça-feira, 11 de setembro de 2012 21:14

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.br

    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.

    quarta-feira, 12 de setembro de 2012 12:19
    Moderador
  • 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

    quarta-feira, 12 de setembro de 2012 13:46

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.

    terça-feira, 11 de setembro de 2012 21:38
  • 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

    terça-feira, 11 de setembro de 2012 21:50
  • 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.

    terça-feira, 11 de setembro de 2012 21:59
  • 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

    terça-feira, 11 de setembro de 2012 22:11
  • 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.

    terça-feira, 11 de setembro de 2012 22:31
  • 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

    terça-feira, 11 de setembro de 2012 22:38
  • Qual a versão do seu SQL?

    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    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.

    quarta-feira, 12 de setembro de 2012 10:59
    Moderador
  • 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

    quarta-feira, 12 de setembro de 2012 11:15
  • 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.br

    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.

    quarta-feira, 12 de setembro de 2012 12:19
    Moderador
  • 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

    quarta-feira, 12 de setembro de 2012 13:46
  • Jefferson, conseguiu?

    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    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.

    quarta-feira, 12 de setembro de 2012 16:32
    Moderador
  • 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

    quarta-feira, 12 de setembro de 2012 20:21
  • 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

    quarta-feira, 12 de setembro de 2012 20:25
  • 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

    quarta-feira, 12 de setembro de 2012 20:43
  • 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

    quinta-feira, 13 de setembro de 2012 04:09