none
Calcular percentual de horas

    Question

  • Bom dia 

    Gostaria de uma ajuda muito grande, pois já busquei em vários fóruns e nada ate agora.

    Bom vamos ao meu problema.

    Preciso fazer uma busca que calcule o total de horas registradas e o percentual em relação as jornada que é de 220h/mes

    segue como tentei só que sem sucesso !

    SELECT AP.ID, AP.CENTRO_DE_CUSTO,DATA,
    convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) / 60 ) + ':' +
    REPLICATE('0', 2 - datalength( ltrim(rtrim( convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) ))) ) + 
    convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) + ':00' as TH,
    RIGHT(CONVERT(VARCHAR,((((SUM(SUBSTRING(ap.TH,1,2) * 60 + SUBSTRING(ap.TH, 4,2))))*100)/13200)),3) AS PERCENTUAL,
    CASE 
    			WHEN AP.CENTRO_DE_CUSTO = MAT.CCPADRAO THEN 'CREDITO'
         		WHEN AP.CENTRO_DE_CUSTO <> MAT.CCPADRAO THEN 'DEBITO'
    		END AS TIPO
    
    
    FROM APROPRIACAO AS AP INNER JOIN MATRICULA AS MAT ON (AP.ID=MAT.ID)
    WHERE MAT.CCPADRAO IS NOT NULL AND AP.CENTRO_DE_CUSTO <> CCPADRAO 
    GROUP BY AP.ID,AP.CENTRO_DE_CUSTO, MAT.CCPADRAO, DATA

    Friday, May 10, 2013 1:33 PM

Answers

  • Resolvi assim ...

    SELECT ID, CENTRO_DE_CUSTO, DATA , TH, (PERCENTUAL/60) AS PERCENTUAL
    		FROM (
    				SELECT TOP 5 AP.ID, AP.CENTRO_DE_CUSTO,DATA,
    				convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) / 60 ) + ':' +
    				REPLICATE('0', 2 - datalength( ltrim(rtrim( convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) ))) ) + 
    				convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) + ':00' as TH,
    				CASE WHEN AP.CENTRO_DE_CUSTO <> MAT.CCPADRAO THEN 'DEBITO' END AS TIPO,
    				RIGHT(CONVERT(VARCHAR,((((SUM(SUBSTRING(TH,1,2) * 60 + SUBSTRING(TH, 4,2))))*13200)/100)),3) AS PERCENTUAL
    				FROM APROPRIACAO AS AP INNER JOIN MATRICULA AS MAT ON (AP.ID=MAT.ID)
    				WHERE MAT.CCPADRAO IS NOT NULL AND AP.CENTRO_DE_CUSTO <> CCPADRAO 
    				GROUP BY AP.ID,AP.CENTRO_DE_CUSTO, MAT.CCPADRAO, DATA
    			 )X
    		GROUP BY ID, CENTRO_DE_CUSTO, DATA , TH, (PERCENTUAL/60)

    aó basta saber se está certo!!!

    • Marked as answer by louco82 Friday, May 10, 2013 6:15 PM
    Friday, May 10, 2013 6:14 PM

All replies

  • Louco82,  qual o seu campo de total de horas trabalhadas ? O calculo teria que ser mais ou menos como abaixo só fazendo a somatoria do total de horas trabalhadas e colocar no lugar da variavel @horas, logicamente dentro da sua query e não necessitaria de variavel.

    Declare @Horas float
    declare @total float
    
    set @Horas = 100 -- total de horas trabalhadas
    set @total = 220 -- total jornada
    
    select @Horas / @total * 100 -- porcentagem completa


    Alexandre Matayosi Conde Mauricio.

    Friday, May 10, 2013 2:32 PM
  • PIOR QUE MEU CAMPO HORA (TH) É VARCHAR, DAI PRECISO FAZER O CONVERT OU O CAST PRA DAI MULTIPLICAR 

    DAI NÃO SEI COMO FAZER ISSO PRA DEPOIS CALCULAR O PERCENTUAL.

    OLHA COMO ESTOU TENTANDO :

    SELECT TOP 5 AP.ID, AP.CENTRO_DE_CUSTO,DATA,
    		convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) / 60 ) + ':' +
    		REPLICATE('0', 2 - datalength( ltrim(rtrim( convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) ))) ) + 
    		convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) + ':00' as TH,
    		CASE WHEN AP.CENTRO_DE_CUSTO <> MAT.CCPADRAO THEN 'DEBITO' END AS TIPO
    		FROM APROPRIACAO AS AP INNER JOIN MATRICULA AS MAT ON (AP.ID=MAT.ID)
    		WHERE MAT.CCPADRAO IS NOT NULL AND AP.CENTRO_DE_CUSTO <> CCPADRAO 
    		GROUP BY AP.ID,AP.CENTRO_DE_CUSTO, MAT.CCPADRAO, DATA


    QUEM ME RETORNA :

    ID                   CENTRO_DE_CUSTO DATA                    TH                                   TIPO
    -------------------- --------------- ----------------------- -----------------------------------------------------------------------------------------------------------------------------------------------
     000232               1.0004.012      2013-03-22 00:00:00     2:30:00                    DEBITO                                                                                                                        000232               1.0004.012      2013-04-04 00:00:00     4:30:00                    DEBITO                                                                                                                        000232               1.0174.001      2013-02-23 00:00:00     7:00:00                    DEBITO                                                                                                                        000275               1.0066.023      2013-04-18 00:00:00     4:55:00                    DEBITO                                                                                                                        000275               1.0053.024      2013-03-18 00:00:00     9:10:00                    DEBITO

    DAI PRECISO FAZER ESSE CALCULO NO CAMPO TH QUE DE MOMENTO É VARCHAR

    SELECT ID, CENTRO_DE_CUSTO, DATA , TH, ((CONVERT( VARCHAR, TH,108)/'220:00:00')*100) AS PERCENTUAL
    FROM (
    		SELECT TOP 5 AP.ID, AP.CENTRO_DE_CUSTO,DATA,
    		convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) / 60 ) + ':' +
    		REPLICATE('0', 2 - datalength( ltrim(rtrim( convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) ))) ) + 
    		convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) + ':00' as TH,
    		CASE WHEN AP.CENTRO_DE_CUSTO <> MAT.CCPADRAO THEN 'DEBITO' END AS TIPO
    		FROM APROPRIACAO AS AP INNER JOIN MATRICULA AS MAT ON (AP.ID=MAT.ID)
    		WHERE MAT.CCPADRAO IS NOT NULL AND AP.CENTRO_DE_CUSTO <> CCPADRAO 
    		GROUP BY AP.ID,AP.CENTRO_DE_CUSTO, MAT.CCPADRAO, DATA
         )X
    GROUP BY ID, CENTRO_DE_CUSTO, DATA , TH

    ISSO ME RETORNA ESSE ERRO!!

    Msg 8117, Level 16, State 1, Line 1
    Operand data type varchar is invalid for divide operator.

    Friday, May 10, 2013 2:53 PM
  • Partindo do principio que voce ja tem a query pronta com a formula correta, o que eu faria seria pegar o resultado da sua primeira query e jogar em uma tabela temporaria, a partir dai mudaria a coluna para um valor numerico para voce poder fazer o calculo em cima deste valor.

    Alexandre Matayosi Conde Mauricio.

    Friday, May 10, 2013 3:10 PM
  • Partindo do principio que voce ja tem a query pronta com a formula correta, o que eu faria seria pegar o resultado da sua primeira query e jogar em uma tabela temporaria, a partir dai mudaria a coluna para um valor numerico para voce poder fazer o calculo em cima deste valor.

    Alexandre Matayosi Conde Mauricio.

    E COMO EU FARIA ISSO ?

    PODES ME AJUDAR COM O EXEMPLO DE ALGUMA FUNÇÃO??

    Friday, May 10, 2013 3:11 PM
  • Da uma olhada no exemplo que criei, criei primeiro uma temporaria (#temp) com a coluna TH como varchar simulando o seu ambiente, a partir disto inserir alguns registros de horas tambem como varchar, coloquei em uma outra temporaria (#temp2) esta coluna ja pegando valores separados de horas e minutos, a partir disto alterei o tipo da coluna de horas e minutos para um tipo numerico, e no select final somei os minutos as horas fazendo o percentual sobre as 220 horas.

    Acho que esse exemplo deve te ajudar a colocar nas suas querys, tente trabalhar com temporarias para fazer a conversão do varchar para numericos e a partir dai fica facil pra voce fazer a conta para pegar o percentual:

    Create table #temp (TH varchar(10))
    
    insert into #temp
    values ('12:30:00'),
    ('85:00:00'),
    ('210:00:00')
    
    select
    	*, 
    	left(TH,CHARINDEX(':',TH) -1) as Hora , 
    	SUBSTRING(TH, CHARINDEX(':',TH) + 1,2) as Minuto
    into #temp2	
    	from #temp
    
    alter table #temp2 alter column Hora float
    alter table #temp2 alter column Minuto int
    
    select
    	TH,
    	Hora,
    	Minuto,
    	TotalHoras = case when Minuto = 0 then Hora / 220 * 100
    				      when Minuto between 1 and 15 then (Hora + 0.3) / 220 * 100
    				      when Minuto between 16 and 30 then (Hora  + 0.5) / 220 * 100
    				      when Minuto between 31 and 45 then (Hora + 0.8) /220 * 100
    				      when Minuto > 45 then (hora + 1) / 220 * 100 end
    	from #temp2


    Alexandre Matayosi Conde Mauricio.

    Friday, May 10, 2013 5:46 PM
  • AINDA ASSIM NÃO FUNCIONOU

    • Marked as answer by louco82 Friday, May 10, 2013 6:14 PM
    • Unmarked as answer by louco82 Friday, May 10, 2013 6:14 PM
    Friday, May 10, 2013 6:00 PM
  • Voce conseguiu separar as horas e minutos em uma temporaria e transformar o campo em numerico ? O segredo é chegar nessa parte, depois que todos os campos estão como numericos não tem por que o sql dar erro de conversão no calculo.

    Alexandre Matayosi Conde Mauricio.

    Friday, May 10, 2013 6:03 PM
  • Resolvi assim ...

    SELECT ID, CENTRO_DE_CUSTO, DATA , TH, (PERCENTUAL/60) AS PERCENTUAL
    		FROM (
    				SELECT TOP 5 AP.ID, AP.CENTRO_DE_CUSTO,DATA,
    				convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) / 60 ) + ':' +
    				REPLICATE('0', 2 - datalength( ltrim(rtrim( convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) ))) ) + 
    				convert(varchar,( sum(datepart(hh,convert(varchar,th,108))) * 60 + sum(datepart(mi,convert(varchar,th,108))) ) % 60 ) + ':00' as TH,
    				CASE WHEN AP.CENTRO_DE_CUSTO <> MAT.CCPADRAO THEN 'DEBITO' END AS TIPO,
    				RIGHT(CONVERT(VARCHAR,((((SUM(SUBSTRING(TH,1,2) * 60 + SUBSTRING(TH, 4,2))))*13200)/100)),3) AS PERCENTUAL
    				FROM APROPRIACAO AS AP INNER JOIN MATRICULA AS MAT ON (AP.ID=MAT.ID)
    				WHERE MAT.CCPADRAO IS NOT NULL AND AP.CENTRO_DE_CUSTO <> CCPADRAO 
    				GROUP BY AP.ID,AP.CENTRO_DE_CUSTO, MAT.CCPADRAO, DATA
    			 )X
    		GROUP BY ID, CENTRO_DE_CUSTO, DATA , TH, (PERCENTUAL/60)

    aó basta saber se está certo!!!

    • Marked as answer by louco82 Friday, May 10, 2013 6:15 PM
    Friday, May 10, 2013 6:14 PM
  • Show de bola, para saber se esta certo costumo pegar alguns casos e fazer a conta manual, se bater esta certo caso contrario tem que rever o codigo...


    Alexandre Matayosi Conde Mauricio.

    Friday, May 10, 2013 6:16 PM