none
Select RRS feed

  • Pergunta

  • Pessoal, boa tarde.
    Tenho o seguinte select

    SET DATEFORMAT DMY
    SELECT
     TbApontamento.Sequencia, TbApontamento.Data, TbApontamento.Atividade, TbApontamento.Categoria, TbApontamento.Motivo,
     TbApontamento.CodTbProjeto, TbApontamento.CodTbColaborador, TbApontamento.Num3SI, TbApontamento.HoraIni,
     TbApontamento.HoraFinal, TbApontamento.TempoTotal, TbApontamento.CodTbApontamento, TbCategoria.CodTbCategoria,
     TbCategoria.NomeCategoria, TbColaborador.CodTbColaborador AS Expr1, TbColaborador.NomeColaborador,
     TbColaborador.loginsistema, TbMotivo.CodTbMotivo, TbMotivo.NomeMotivo, TbProjeto.CodTbProjeto AS Expr2,
     TbProjeto.NomeProjeto, TbProjeto.CodTbProjetoOld , sum(convert(int,TbApontamento.TempoTotal))

    FROM
     TbApontamento
     INNER JOIN TbCategoria ON TbApontamento.Categoria = TbCategoria.CodTbCategoria
     INNER JOIN  TbColaborador ON TbApontamento.CodTbColaborador = TbColaborador.CodTbColaborador
     INNER JOIN   TbMotivo ON TbApontamento.Motivo = TbMotivo.CodTbMotivo 
     LEFT JOIN  TbProjeto ON TbApontamento.CodTbProjeto = TbProjeto.CodTbProjeto

    group by

     TbApontamento.Sequencia, TbApontamento.Data, TbApontamento.Atividade, TbApontamento.Categoria, TbApontamento.Motivo,
     TbApontamento.CodTbProjeto, TbApontamento.CodTbColaborador, TbApontamento.Num3SI, TbApontamento.HoraIni,
     TbApontamento.HoraFinal, TbApontamento.TempoTotal, TbApontamento.CodTbApontamento, TbCategoria.CodTbCategoria,
     TbCategoria.NomeCategoria, TbColaborador.CodTbColaborador , TbColaborador.NomeColaborador,
     TbColaborador.loginsistema, TbMotivo.CodTbMotivo, TbMotivo.NomeMotivo, TbProjeto.CodTbProjeto ,
     TbProjeto.NomeProjeto, TbProjeto.CodTbProjetoOld



    O problema que estou tentando fazer um sum no TbApontamento.TempoTotal que é do tipo string e que armazena uma quantidade x de horas no seguinte formato 01:00:00 .
    Por exemplo 59 minutos ficaria armazenado da seguinte maneira 00:59:00.
    Dessa forma ao tentar o comando convert(int,TbApontamento.TempoTotal) eu recebo o seguinte erro:
    Syntax error converting the varchar value '01:00:00  ' to a column of data type int.

    Alguem tem uma sugestão de como eu posso fazer esse select sem ter que mudar o tipo do dado na tabela?
    Mesmo porque essa opção não é viável.

    Grato desde já
    Gustavo


    Analista Programador
    sexta-feira, 5 de junho de 2009 18:15

Respostas

  • Boa Tarde,

    O uso de CTEs não é compatível com o SQL Server 2000. Se for o caso você terá que optar pela subquery. Adaptando o exemplo do Marcelo teríamos:

    declare @tabela as table (nome varchar(15), time char(8))
    insert into @tabela values('Marcelo','00:02:23')
    insert into @tabela values('Fulano','00:01:00')
    insert into @tabela values('Marcelo','01:03:00')
    
    select nome, convert(char(8),(DATEADD(hh, hor,(DATEADD(mi, min,(DATEADD(ss, sec, '00:00:00')))))),108)
    from
    (   
        select nome,sum(hor) as hor,sum([min]) as [min] ,sum(sec) as sec
        from (SELECT nome,cast(substring(time,1,2) as int) as hor, cast(substring(time,4,2) as int) as min, cast(substring(time,7,2) as int) as sec
                FROM @tabela
            ) a
        group by nome
    ) as cte_dados;

    [ ]s,

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

    Usando o SSIS para importar várias planilhas de um mesmo arquivo Excel para o SQL Server
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!591.entry
    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar segunda-feira, 8 de junho de 2009 18:09
    • Marcado como Resposta gusbr quarta-feira, 10 de junho de 2009 17:56
    sexta-feira, 5 de junho de 2009 19:55
  • Acho que consegui!
    Mesmo assim obrigado!
    O select que eu fiz ficou

    select NomeColaborador,Data, convert(char(8),(DATEADD(hh, hor,(DATEADD(mi, min,(DATEADD(ss, sec, '00:00:00')))))),108)
    from
    (  
        select NomeColaborador,Data, sum(hor) as hor,sum([min]) as [min] ,sum(sec) as sec
        from (SELECT TbColaborador.NomeColaborador, Data, cast(substring(TempoTotal,1,2) as int) as hor, cast(substring(TempoTotal,4,2) as int) as min,
       cast(substring(TempoTotal,7,2) as int) as sec
                FROM TbApontamento
       INNER JOIN  TbColaborador ON TbApontamento.CodTbColaborador = TbColaborador.CodTbColaborador
            ) a

    group by NomeColaborador,Data


       
    ) as cte_dados
    order by NomeColaborador, Data


    Analista Programador
    • Marcado como Resposta gusbr quarta-feira, 10 de junho de 2009 17:55
    • Não Marcado como Resposta gusbr terça-feira, 16 de junho de 2009 13:36
    • Marcado como Resposta gusbr terça-feira, 16 de junho de 2009 13:37
    quarta-feira, 10 de junho de 2009 17:55

Todas as Respostas

  • Ola Gusdorta

    vc não pode fazer um sum de um varchar e nem de datetime, e tbm nao pode converter '01:01:00' p/ int,
    Abaixo um exemplo para soma entre datas.
    declare @tabela as table (nome varchar(15), time char(8))
    insert into @tabela values('Marcelo','00:02:23')
    insert into @tabela values('Fulano','00:01:00')
    insert into @tabela values('Marcelo','01:03:00')
    ;with cte_dados (nome,hor,min,sec)
    as
    (   
        select nome,sum(hor),sum(min),sum(sec)
        from (SELECT nome,cast(substring(time,1,2) as int) as hor, cast(substring(time,4,2) as int) as min, cast(substring(time,7,2) as int) as sec
                FROM @tabela
            ) a
        group by nome
    )
    select nome, convert(char(8),(DATEADD(hh, hor,(DATEADD(mi, min,(DATEADD(ss, sec, '00:00:00')))))),108)
    from cte_dados;
    
    OBS: esta solução funciona no SQL 2005.

    Adicional a isto leia o blog do Gustavo
    Como realizar cálculos com horas no SQL Server – Parte I
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!516.entry

    Como realizar cálculos com horas no SQL Server – Parte II
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!532.entry

    Como realizar cálculos com horas no SQL Server – Parte III
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!534.entry

    Como realizar cálculos com horas no SQL Server – Parte IV
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!536.entry

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    sexta-feira, 5 de junho de 2009 18:36
    Moderador
  • Meu banco é sql2000, acho que não funciona né?
    E agora?


    Analista Programador
    sexta-feira, 5 de junho de 2009 19:22
  • Boa Tarde,

    O uso de CTEs não é compatível com o SQL Server 2000. Se for o caso você terá que optar pela subquery. Adaptando o exemplo do Marcelo teríamos:

    declare @tabela as table (nome varchar(15), time char(8))
    insert into @tabela values('Marcelo','00:02:23')
    insert into @tabela values('Fulano','00:01:00')
    insert into @tabela values('Marcelo','01:03:00')
    
    select nome, convert(char(8),(DATEADD(hh, hor,(DATEADD(mi, min,(DATEADD(ss, sec, '00:00:00')))))),108)
    from
    (   
        select nome,sum(hor) as hor,sum([min]) as [min] ,sum(sec) as sec
        from (SELECT nome,cast(substring(time,1,2) as int) as hor, cast(substring(time,4,2) as int) as min, cast(substring(time,7,2) as int) as sec
                FROM @tabela
            ) a
        group by nome
    ) as cte_dados;

    [ ]s,

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

    Usando o SSIS para importar várias planilhas de um mesmo arquivo Excel para o SQL Server
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!591.entry
    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar segunda-feira, 8 de junho de 2009 18:09
    • Marcado como Resposta gusbr quarta-feira, 10 de junho de 2009 17:56
    sexta-feira, 5 de junho de 2009 19:55
  • Bom dia.,
    Vou tentar adaptar meu select com a dica acima e volto para postar o resultado.
    Grato desde já
    Gustavo
    Analista Programador
    segunda-feira, 8 de junho de 2009 13:33
  • Pessoal, boa tarde!
    Não consegui de jeito nenhum adaptar o select do meu primeiro post com a dica enviada pelo gustavo maia.
    Precisava que o select fizesse o SUM do campo TbApontamento.TempoTotal

    Alguem poderia me auxiliar?
    Grato desde já
    Gustavo
    Analista Programador
    quarta-feira, 10 de junho de 2009 17:34
  • Ola Gustavo,

    Poste um exemplo de como vc esta fazendo com um select em tab temporaria ou variavel table, semelhante ao que fiz no post acima.

    Att.
    Marcelo Fernandes
    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    quarta-feira, 10 de junho de 2009 17:50
    Moderador
  • Acho que consegui!
    Mesmo assim obrigado!
    O select que eu fiz ficou

    select NomeColaborador,Data, convert(char(8),(DATEADD(hh, hor,(DATEADD(mi, min,(DATEADD(ss, sec, '00:00:00')))))),108)
    from
    (  
        select NomeColaborador,Data, sum(hor) as hor,sum([min]) as [min] ,sum(sec) as sec
        from (SELECT TbColaborador.NomeColaborador, Data, cast(substring(TempoTotal,1,2) as int) as hor, cast(substring(TempoTotal,4,2) as int) as min,
       cast(substring(TempoTotal,7,2) as int) as sec
                FROM TbApontamento
       INNER JOIN  TbColaborador ON TbApontamento.CodTbColaborador = TbColaborador.CodTbColaborador
            ) a

    group by NomeColaborador,Data


       
    ) as cte_dados
    order by NomeColaborador, Data


    Analista Programador
    • Marcado como Resposta gusbr quarta-feira, 10 de junho de 2009 17:55
    • Não Marcado como Resposta gusbr terça-feira, 16 de junho de 2009 13:36
    • Marcado como Resposta gusbr terça-feira, 16 de junho de 2009 13:37
    quarta-feira, 10 de junho de 2009 17:55