none
T-SQL retornando dias agrupados seguidos de suas horas RRS feed

  • Pergunta

  • Bom dia,

    tenho uma query que retorna a seguinte informação

    Datas
    2009-05-25 12:06:00.000
    2009-05-25 13:42:00.000
    2009-05-25 18:10:00.000
    2009-05-21 07:51:00.000
    2009-05-21 12:04:00.000
    2009-05-21 17:01:00.000
    2009-05-21 18:09:00.000
    2009-05-20 13:57:00.000
    2009-05-20 18:14:00.000
    2009-05-19 09:50:00.000
    2009-05-19 12:08:00.000
    2009-05-19 12:10:00.000
    2009-05-19 12:12:00.000
    2009-05-19 12:13:00.000
    2009-05-19 12:14:00.000
    2009-05-19 12:15:00.000


    ---------------- SQL Abaixo -------------------

     select
      DatePoint
     from
      PointsColaborator 
     where ColaboratorID = @idCol
      and DatePoint between @dateStart and @dateEnd
     order by DatePoint DESC


    Gostaria de um SQL que retornasse da seguinte forma


    Datas        Pontos
    2009-05-25   12:06:00, 13:42:00, 18:10:00
    2009-05-21   07:51:00, 12:04:00, 17:01:00, 18:09:00
    2009-05-20   13:57:00, 18:14:00
    2009-05-19   09:50:00, 12:08:00, 12:10:00, 12:12:00, 12:13:00, 12:14:00, 12:15:00


    Os dias agrupados para cada dia as suas batidas de ponto


    Riderman
    terça-feira, 26 de maio de 2009 14:32

Respostas

  • Bom Dia,

    Se for 2005 ou superior tente o seguinte

    declare @t table (horario datetime)
    
    insert into @t values ('2009-05-25 12:06:00.000')
    insert into @t values ('2009-05-25 13:42:00.000')
    insert into @t values ('2009-05-25 18:10:00.000')
    insert into @t values ('2009-05-21 07:51:00.000')
    insert into @t values ('2009-05-21 12:04:00.000')
    insert into @t values ('2009-05-21 17:01:00.000')
    insert into @t values ('2009-05-21 18:09:00.000')
    insert into @t values ('2009-05-20 13:57:00.000')
    insert into @t values ('2009-05-20 18:14:00.000')
    insert into @t values ('2009-05-19 09:50:00.000')
    insert into @t values ('2009-05-19 12:08:00.000')
    insert into @t values ('2009-05-19 12:10:00.000')
    insert into @t values ('2009-05-19 12:12:00.000')
    insert into @t values ('2009-05-19 12:13:00.000')
    insert into @t values ('2009-05-19 12:14:00.000')
    insert into @t values ('2009-05-19 12:15:00.000')
    
    ;WITH Dias (Dia) As (
    	SELECT DISTINCT
    		REPLACE(CONVERT(CHAR(10),horario,102),'.','-') FROM @t),
    
    Horarios (Dia, Horario) As (
    	SELECT
    		REPLACE(CONVERT(CHAR(10),horario,102),'.','-'),
    		CONVERT(CHAR(8),horario,108)
    	FROM @t),
    
    Res As (
    
    SELECT D.Dia,
    	
    	(SELECT Horario As h FROM Horarios As H WHERE D.Dia = H.Dia
    	FOR XML RAW('Hs')) As Horarios
    FROM Dias As D)
    
    SELECT Dia,
    	REPLACE(
    		REPLACE(Horarios,'<Hs h="',''),'"/>',' ; ')
    FROM Res

    Adicionalmente recomendo os links abaixo:

    Concatenando Registros
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!185.entry

    SQL Server 2005 T-SQL Tips: Concatenando e Agrupando
    http://www.plugmasters.com.br/sys/materias/869/1/SQL-Server-2005-T-SQL-Tips%3A-Concatenando-e-Agrupando

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como executar tarefas ao iniciar o SQL Server ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!570.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Riderman quinta-feira, 4 de junho de 2009 18:30
    terça-feira, 26 de maio de 2009 14:53

Todas as Respostas