none
Calcular Horas Uteis no SQL RRS feed

  • Pergunta

  • Estou quebrando a cabeça e não estou conseguindo achar uma solução que funcione legal, ja vi os seguintes posts...

    «1» http://social.msdn.microsoft.com/Forums/pt-BR/520/thread/ed25d606-4546-4ca8-ab0c-a62a286f25a3

    «2» http://social.msdn.microsoft.com/Forums/pt-BR/520/thread/08ec3fab-778f-4d7f-9fb5-ef5cd02b1699

    Porem não funcionou da maneira que esperava

    No exemplo «1» se eu colocar a

    @DATINI = '2012-05-14 07:00:00'

    @DATIM = GETDATE() = '2012-05-15 10:23:00'

    Retorna certo  -->  12:23 (09 Horas do periodo anterior mais 03:23 do dia atual),

    mas se eu colocar no @DATINI = '2012-05-14 16:00:00'

    Retorna Errado --> 03:23 (Ignora dia anterior)

    PRINT( CONVERT(VARCHAR, CONVERT(int, @minutos / 60)) + ':' + RIGHT(CONVERT(VARCHAR, CONVERT(int, @minutos % 60) + 100), 2) )

    No Exemplo «2» tambem não rodou

    terça-feira, 15 de maio de 2012 13:28

Respostas

  • Tive uma ideia, teste ai se da certo

    ALTER FUNCTION DATA_MINUTOS (@DATINI DATETIME, @DATFIM DATETIME,@ALMINI TIME, @ALMFIM TIME)
    RETURNS INT
    BEGIN
    	DECLARE @ALMOCO AS INT = 0
    	
    	IF CAST(@DATINI AS TIME) <= @ALMINI and CAST(@DATFIM AS TIME) >= @ALMFIM 
    	BEGIN 
    		SET @ALMOCO = DATEDIFF(MINUTE,@ALMINI,@ALMFIM)
    	END 
    
    
    	DECLARE @DATINIP DATETIME, @DATFIMP DATETIME, @DIASINUTEIS INT, @MINUTOS INT
    	SET @DIASINUTEIS = 0
    	
    	WHILE DATEPART(DW,@DATINI) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATINI = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI+1,112))+'07:00'
    	END
    
    	WHILE DATEPART(DW,@DATFIM) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATFIM = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM-1,112))+'17:00'  
    	END
    
    	SET @DATINIP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI,112))+'07:00'
    	SET @DATFIMP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM,112))+'07:00'
    
    	SET @MINUTOS = DATEDIFF(DD,@DATINI,@DATFIM) * 540 /*540 pois se a diferença é = a um dia logo ele obrigatoriamente fez só nove horas*/  + (	(SELECT CASE	WHEN DATEDIFF(MI,@DATINIP,@DATINI) < 0  THEN 0 
    																			WHEN DATEDIFF(MI,@DATINIP,@DATINI) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATINIP,@DATINI) END ) * - 1 +
    
    															(SELECT CASE	WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) < 0 THEN 0 
    																			WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATFIMP,@DATFIM) END ) )
    
    	WHILE @DATINI < = @DATFIM
    	BEGIN
    		IF DATEPART(DW,@DATINI) IN (1,7) 
    		BEGIN
    			SET @DIASINUTEIS = @DIASINUTEIS + 1
    		END
    		SET @DATINI = @DATINI +1 		
    	END		
    
    
    	SET @MINUTOS = (SELECT CASE WHEN @MINUTOS < (@DIASINUTEIS * 600) THEN 0 
    								ELSE @MINUTOS - (@DIASINUTEIS * 600) END)
    			
    	RETURN @MINUTOS - @ALMOCO
    	
    END
    
    
    SELECT DATEADD(MINUTE,DBO.DATA_MINUTOS('2012-05-14 11:00:00','2012-05-15 15:00:00','12:00:00','13:00:00'),0)


    Att.
    Lukas Baldan


    terça-feira, 15 de maio de 2012 18:39

Todas as Respostas

  • Olá Savione,

    Podemos resolver este problema com a função DATEDIFF, com ela você obtém a diferença entre 2 datas específicas.

    exemplo:

    declare @data1 datetime, @data2 datetime
    set @data1 = '2012-05-14 12:10:01'
    set @data2 = '2012-05-15 10:51:00'
    
    select DATEDIFF(hour,@data1,@data2) as horas
    select DATEDIFF(minute,@data1,@data2) as minutos
    select DATEDIFF(Second,@data1,@data2) as segundos

    Se útil, Classifique 

    Abraços


    Dhiego Piroto - MCP | MCTS SQL Server 2008 Developer | Email: dhiegopiroto@gmail.com Blog: http://dhiegopiroto.wordpress.com/


    • Editado DhiegoPiroto terça-feira, 15 de maio de 2012 13:54
    terça-feira, 15 de maio de 2012 13:53
  • Ola Dhiego,

    Até ai tudo bem, mas por exemplo dentro do dia tenho um periodo de horario  exemplo (Entrada: 07:00, Saida Almoco: 12:30, Retorno Almoco: 13:30, Saida: 17:00) no total de 09 Horas.

    Tenho que calcular a quantidade de horas baseado nesse "filtro", sendo que a data pode variar entre dias ou nao.

    terça-feira, 15 de maio de 2012 14:02
  • Olá Savione,

    O exemplo 1 está correto

    o Valor 03:23 esta correto pois o horário comercial no caso começa as 08:00 e termina as 17:00.

    Inicio '2012-05-14 16:00:00' 01:00 de diferença + @DATIM = GETDATE() = '2012-05-15 10:23:00' 02:30 de diferença  = 03:30 de diferença

    Quando começa e termina suas horas??


    Att.
    Lukas Baldan

    terça-feira, 15 de maio de 2012 14:07
  • No meu caso alterei para 07:00 o horario de inicio e permaneceu as 17:00 o horario de fim.
    • Editado Savoine terça-feira, 15 de maio de 2012 14:10
    terça-feira, 15 de maio de 2012 14:09
  • Neste caso voce aumentou 1 hora no intervalo, logo você tem que adicionar 60 minutos a quantidade de horas do dia,

    teste este função

    CREATE FUNCTION DATA_MINUTOS (@DATINI DATETIME, @DATFIM DATETIME)
    RETURNS INT
    BEGIN
    	DECLARE @DATINIP DATETIME, @DATFIMP DATETIME, @DIASINUTEIS INT, @MINUTOS INT
    	SET @DIASINUTEIS = 0
    	
    	WHILE DATEPART(DW,@DATINI) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATINI = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI+1,112))+'07:00'
    	END
    
    	WHILE DATEPART(DW,@DATFIM) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATFIM = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM-1,112))+'17:00'  
    	END
    
    	SET @DATINIP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI,112))+'07:00'
    	SET @DATFIMP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM,112))+'07:00'
    
    	SET @MINUTOS = DATEDIFF(DD,@DATINI,@DATFIM) * 600  + (	(SELECT CASE	WHEN DATEDIFF(MI,@DATINIP,@DATINI) < 0  THEN 0 
    																			WHEN DATEDIFF(MI,@DATINIP,@DATINI) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATINIP,@DATINI) END ) * - 1 +
    
    															(SELECT CASE	WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) < 0 THEN 0 
    																			WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATFIMP,@DATFIM) END ) )
    
    	WHILE @DATINI < = @DATFIM
    	BEGIN
    		IF DATEPART(DW,@DATINI) IN (1,7) 
    		BEGIN
    			SET @DIASINUTEIS = @DIASINUTEIS + 1
    		END
    		SET @DATINI = @DATINI +1 		
    	END		
    
    
    	SET @MINUTOS = (SELECT CASE WHEN @MINUTOS < (@DIASINUTEIS * 600) THEN 0 
    								ELSE @MINUTOS - (@DIASINUTEIS * 600) END)
    			
    	RETURN @MINUTOS
    
    END


    Att.
    Lukas Baldan


    terça-feira, 15 de maio de 2012 14:21
  • Mas lucas observe o seguinte se eu colocar 07:00 no lugar das 16:00 acrescenta 1 hora a mais do que é real exemplo:

    @DATINI = '2012-05-14 07:00:00'

    @DATFIM =  '2012-05-15 11:35:00'

    PERIODO ANTERIOR = 9 HORAS

    DIA ATUAL = 04:35

    TOTAL = 13:35 mas no resultado da alteracao q voce colocou sai 14:35, se possível realiza o teste para vc verificar.


    • Editado Savoine terça-feira, 15 de maio de 2012 14:39
    terça-feira, 15 de maio de 2012 14:37
  • Savione,

    o periodo anterior é 10 horas das 07:00 da manhã as 17:00 são 10 horas de diferença

    + 04:35 = 14:35


    Att.
    Lukas Baldan


    terça-feira, 15 de maio de 2012 14:44
  • Lukas, acredito que não estou conseguindo te explicar, vamos la...

    1º - volte ao 540 os campos que voce colocou 600

    2º - execute com o @DATINI = '2012-05-14 07:00:00' / @DATFIM '2012-05-15 11:48:00' - VAI RETORNAR  13:48 (CORRETO)

    3º - execute novamente do mesmo jeito porem com o @DATINI = '2012-05-14 16:00:00' / @DATFIM '2012-05-15 11:49:00' - VAI RETORNAR 4:49 (ERRADO, deveria retornar 5:49).

    Entendeu o que estou tentando explicar? a mesma funcao dependendo do horario ela retorna o valor certo ou nao.

    terça-feira, 15 de maio de 2012 14:50
  • Olá Savoine,

    Tente o seguinte:

    declare @data1 datetime, @data2 datetime, @data3 datetime, @data4 datetime
    set @data1 = '2012-05-15 09:10:01'
    set @data2 = '2012-05-15 12:51:00'
    set @data3 = '2012-05-15 13:30:00'
    set @data4 = '2012-05-15 18:01:00'
    
    select (DATEDIFF(minute,@data1,@data4)) - (DATEDIFF(minute,@data3,@data2)) as horas
    
    
    

    Se útil, Classifique


    Dhiego Piroto - MCP | MCTS SQL Server 2008 Developer | Email: dhiegopiroto@gmail.com Blog: http://dhiegopiroto.wordpress.com/

    terça-feira, 15 de maio de 2012 15:02
  • Savoine, o que eu não estou entendendo é

    "No meu caso alterei para 07:00 o horario de inicio e permaneceu as 17:00 o horario de fim."

    Logo são 10 horas uteis por dia e não 9 horas certo???

    das 07 as 11:48 são 04:48 certo???

    pelo que eu entendi a funçao usa este numero 540 que equivale a 540/60 = 9 horas, ou seja, das 08:00 as 17:00 são 9 horas de intervalo,

    como você diminuiu para 07:00 eu aumentei para 600 que /60 = 10 horas de intervalo.


    Att.
    Lukas Baldan

    terça-feira, 15 de maio de 2012 15:08
  • Dhiego, no caso eu não tenho "varias" datas eu tenho um periodo, eu sei quando foi "criado" e quando foi "encerrado" preciso fazer o calculo das horas uteis dentro desse periodo (o periodo pode ser o mesmo dia ou varios dias ou meses)
    • Editado Savoine terça-feira, 15 de maio de 2012 15:10
    terça-feira, 15 de maio de 2012 15:09
  • Então,

    data1 = entrada trabalho

    data2 = saida almoço

    data3 = volta almoço

    data4= saida trabalho

    logo, basta preencher e você terá a quantidade de minutos trabalhados (descontando almoço)

    se você colocar que a data1 é de 1 mes atrás e a data4 hoje, os minutos serão computados


    Dhiego Piroto - MCP | MCTS SQL Server 2008 Developer | Email: dhiegopiroto@gmail.com Blog: http://dhiegopiroto.wordpress.com/


    • Editado DhiegoPiroto terça-feira, 15 de maio de 2012 15:13
    terça-feira, 15 de maio de 2012 15:13
  • Porem Lukas nesse tempo existe 1 hora de almoco por ex. entende, como deduzir intao.
    terça-feira, 15 de maio de 2012 16:41
  • Entendi Dhiego, mas no caso posso ter varios dias como pegar o data2/data3 todo dia? pq no exemplo esta especifico para 1 dia.
    terça-feira, 15 de maio de 2012 16:43
  • Savione,

    Sumarize os horarios de almoço de um determinado intervalo. Depois sumarize o tempo trabalhado, (você pode fazer um cursor, por exemplo). Subtraia o tempo total de almoço do tempo total trabalhado.

    Se útil, Classifique.


    Dhiego Piroto - MCP | MCTS SQL Server 2008 Developer | Email: dhiegopiroto@gmail.com Blog: http://dhiegopiroto.wordpress.com/

    terça-feira, 15 de maio de 2012 16:56
  • SAVIONE,

    Veja se este exemplo careia sua mente.

    CREATE TABLE #TEMPO (
    	ID INT IDENTITY(1,2),
    	ENTRADA DATETIME, 
    	SAIDA_ALMOCO DATETIME, 
    	VOLTA_ALMOCO DATETIME,
    	SAIDA DATETIME
    )
    GO
    INSERT INTO #TEMPO VALUES
    ('2012-05-15 09:15:00','2012-05-15 12:01:00', '2012-05-15 12:50:00', '2012-05-15 18:02:00'),
    ('2012-05-16 09:30:00','2012-05-16 12:00:00', '2012-05-16 12:10:00', '2012-05-16 18:20:00'),
    ('2012-05-17 08:00:00','2012-05-17 11:30:00', '2012-05-17 12:21:00', '2012-05-17 17:59:00')
    GO
    select SUM((DATEDIFF(minute,ENTRADA,SAIDA)) - (DATEDIFF(minute,SAIDA_ALMOCO,VOLTA_ALMOCO)))/60 AS HORAS  FROM #TEMPO

    Se útil, Classifique.


    Dhiego Piroto - MCP | MCTS SQL Server 2008 Developer | Email: dhiegopiroto@gmail.com Blog: http://dhiegopiroto.wordpress.com/

    terça-feira, 15 de maio de 2012 17:02
  • Savione,

    entendi agora,

    neste caso o script não consegue calcular este horario,

    esse horario tem que ser fixo qual seria este horario de almoço?


    Att.
    Lukas Baldan

    terça-feira, 15 de maio de 2012 18:01
  • Lukas,

    Na teoria ele deveria ser especificado @SAIDAALMOCO = '12:30' @RETORNOALMOCO = '13:30'

    Mas se for complicar muito ele pode ser FIXO @HORAALMOCO = 60

    terça-feira, 15 de maio de 2012 18:16
  • Savoine,

    fiz uma alteração

    ALTER FUNCTION DATA_MINUTOS (@DATINI DATETIME, @DATFIM DATETIME)
    RETURNS INT
    BEGIN
    	DECLARE @DATINIP DATETIME, @DATFIMP DATETIME, @DIASINUTEIS INT, @MINUTOS INT
    	SET @DIASINUTEIS = 0
    	
    	WHILE DATEPART(DW,@DATINI) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATINI = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI+1,112))+'07:00'
    	END
    
    	WHILE DATEPART(DW,@DATFIM) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATFIM = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM-1,112))+'17:00'  
    	END
    
    	SET @DATINIP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI,112))+'07:00'
    	SET @DATFIMP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM,112))+'07:00'
    
    	SET @MINUTOS = DATEDIFF(DD,@DATINI,@DATFIM) * 540 /*540 pois se a diferença é = a um dia logo ele obrigatoriamente fez nove horas*/  + (	(SELECT CASE	WHEN DATEDIFF(MI,@DATINIP,@DATINI) < 0  THEN 0 
    																			WHEN DATEDIFF(MI,@DATINIP,@DATINI) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATINIP,@DATINI) END ) * - 1 +
    
    															(SELECT CASE	WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) < 0 THEN 0 
    																			WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATFIMP,@DATFIM) END ) )
    
    	WHILE @DATINI < = @DATFIM
    	BEGIN
    		IF DATEPART(DW,@DATINI) IN (1,7) 
    		BEGIN
    			SET @DIASINUTEIS = @DIASINUTEIS + 1
    		END
    		SET @DATINI = @DATINI +1 		
    	END		
    
    
    	SET @MINUTOS = (SELECT CASE WHEN @MINUTOS < (@DIASINUTEIS * 600) THEN 0 
    								ELSE @MINUTOS - (@DIASINUTEIS * 600) END)
    			
    	RETURN @MINUTOS
    
    END
    

    No entanto por exemplo

    SELECT DATEADD(MINUTE,DBO.DATA_MINUTOS('2012-05-15 11:00:00','2012-05-15 14:00:00'),0)

    ele não vai considerar a hora de almoço só o dia cheio

    pelo menos te da uma ideia de como fazer


    Att.
    Lukas Baldan


    terça-feira, 15 de maio de 2012 18:28
  • Tive uma ideia, teste ai se da certo

    ALTER FUNCTION DATA_MINUTOS (@DATINI DATETIME, @DATFIM DATETIME,@ALMINI TIME, @ALMFIM TIME)
    RETURNS INT
    BEGIN
    	DECLARE @ALMOCO AS INT = 0
    	
    	IF CAST(@DATINI AS TIME) <= @ALMINI and CAST(@DATFIM AS TIME) >= @ALMFIM 
    	BEGIN 
    		SET @ALMOCO = DATEDIFF(MINUTE,@ALMINI,@ALMFIM)
    	END 
    
    
    	DECLARE @DATINIP DATETIME, @DATFIMP DATETIME, @DIASINUTEIS INT, @MINUTOS INT
    	SET @DIASINUTEIS = 0
    	
    	WHILE DATEPART(DW,@DATINI) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATINI = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI+1,112))+'07:00'
    	END
    
    	WHILE DATEPART(DW,@DATFIM) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATFIM = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM-1,112))+'17:00'  
    	END
    
    	SET @DATINIP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI,112))+'07:00'
    	SET @DATFIMP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM,112))+'07:00'
    
    	SET @MINUTOS = DATEDIFF(DD,@DATINI,@DATFIM) * 540 /*540 pois se a diferença é = a um dia logo ele obrigatoriamente fez só nove horas*/  + (	(SELECT CASE	WHEN DATEDIFF(MI,@DATINIP,@DATINI) < 0  THEN 0 
    																			WHEN DATEDIFF(MI,@DATINIP,@DATINI) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATINIP,@DATINI) END ) * - 1 +
    
    															(SELECT CASE	WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) < 0 THEN 0 
    																			WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATFIMP,@DATFIM) END ) )
    
    	WHILE @DATINI < = @DATFIM
    	BEGIN
    		IF DATEPART(DW,@DATINI) IN (1,7) 
    		BEGIN
    			SET @DIASINUTEIS = @DIASINUTEIS + 1
    		END
    		SET @DATINI = @DATINI +1 		
    	END		
    
    
    	SET @MINUTOS = (SELECT CASE WHEN @MINUTOS < (@DIASINUTEIS * 600) THEN 0 
    								ELSE @MINUTOS - (@DIASINUTEIS * 600) END)
    			
    	RETURN @MINUTOS - @ALMOCO
    	
    END
    
    
    SELECT DATEADD(MINUTE,DBO.DATA_MINUTOS('2012-05-14 11:00:00','2012-05-15 15:00:00','12:00:00','13:00:00'),0)


    Att.
    Lukas Baldan


    terça-feira, 15 de maio de 2012 18:39
  • Lukas valeu, eu soh mudei o seguinte senao dava erro na hora de converter a quantidade de hora. segue a mudanda

    DECLARE @DATINI DATETIME
    DECLARE @DATFIM DATETIME
    
    set @DATINI = '2012-05-14 07:00:00'
    set @DATFIM = '2012-05-17 20:00:00'
    
    DECLARE @ALMINI TIME
    DECLARE @ALMFIM TIME
    
    SET @ALMINI = '12:30'
    SET @ALMFIM = '13:30'
    
    
    	DECLARE @ALMOCO AS INT = 0
    	
    	IF CAST(@DATINI AS TIME) <= @ALMINI and CAST(@DATFIM AS TIME) >= @ALMFIM 
    	BEGIN 
    		SET @ALMOCO = DATEDIFF(MINUTE,@ALMINI,@ALMFIM)
    	END 
    
    
    	DECLARE @DATINIP DATETIME, @DATFIMP DATETIME, @DIASINUTEIS INT, @MINUTOS INT
    	SET @DIASINUTEIS = 0
    	
    	WHILE DATEPART(DW,@DATINI) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATINI = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI+1,112))+'07:00'
    	END
    
    	WHILE DATEPART(DW,@DATFIM) IN (1,7) AND @DATINI < @DATFIM
    	BEGIN
    		SET @DATFIM = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM-1,112))+'17:00'  
    	END
    
    	SET @DATINIP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATINI,112))+'07:00'
    	SET @DATFIMP = CONVERT(DATETIME,CONVERT(VARCHAR,@DATFIM,112))+'07:00'
    
    	SET @MINUTOS = DATEDIFF(DD,@DATINI,@DATFIM) * 540 /*540 pois se a diferença é = a um dia logo ele obrigatoriamente fez só nove horas*/  + (	(SELECT CASE	WHEN DATEDIFF(MI,@DATINIP,@DATINI) < 0  THEN 0 
    																			WHEN DATEDIFF(MI,@DATINIP,@DATINI) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATINIP,@DATINI) END ) * - 1 +
    
    															(SELECT CASE	WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) < 0 THEN 0 
    																			WHEN DATEDIFF(MI,@DATFIMP,@DATFIM) > 600 THEN 600 
    																			ELSE DATEDIFF(MI,@DATFIMP,@DATFIM) END ) )
    
    	WHILE @DATINI < = @DATFIM
    	BEGIN
    		IF DATEPART(DW,@DATINI) IN (1,7) 
    		BEGIN
    			SET @DIASINUTEIS = @DIASINUTEIS + 1
    		END
    		SET @DATINI = @DATINI +1 		
    	END		
    
    
    	SET @MINUTOS = (SELECT CASE WHEN @MINUTOS < (@DIASINUTEIS * 600) THEN 0 
    								ELSE @MINUTOS - (@DIASINUTEIS * 600) END)
    			
    	set @MINUTOS = @MINUTOS - @ALMOCO
    	
    	SELECT (CONVERT(VARCHAR, CONVERT(int, @minutos / 60)) + ':' + RIGHT(CONVERT(VARCHAR, CONVERT(int, @minutos % 60) + 100), 2) )
    	
    /*SELECT CONVERT(TIME,DATEADD(MINUTE,(select @MINUTOS - @ALMOCO),0))*/

    terça-feira, 15 de maio de 2012 19:23
  • Lukas, muito obrigado me ajudou muito, mas depois começou a dar problema novamente, ai montei ele novamente padronizado e segue novo modelo da solução.

    DECLARE @dInicial DATETIME
    DECLARE @dInicialTemp DATETIME
    DECLARE @dFinal DATETIME
    DECLARE @dFinalTemp DATETIME
    
    DECLARE @tHoraEntrada TIME
    DECLARE @tSaidaAlmoco TIME
    DECLARE @tRetornoAlmoco TIME
    DECLARE @tHoraSaida TIME
    
    DECLARE @iAlmoco INT = 0
    DECLARE @iDiasInuteis INT = 0
    DECLARE @iMinutos INT = 0
    DECLARE @iMinutosUteisDia INT = 0
    DECLARE @iMinutosTotaisDia INT = 0
    
    SET @dInicial       = '2012-05-15 16:00:00'
    SET @dFinal         = GETDATE()
    SET @tHoraEntrada   = '07:00'
    SET @tSaidaAlmoco   = '12:30'
    SET @tRetornoAlmoco = '13:30'
    SET @tHoraSaida	    = '17:00'
    
    /*PRINT('DATA INICIAL: ' + CONVERT(VARCHAR, @dInicial)) */
    /*PRINT('DATA FINAL: ' + CONVERT(VARCHAR, @dFinal)) */
    
    IF CAST(@dInicial AS TIME) <= @tSaidaAlmoco AND CAST(@dFinal AS TIME) >= @tRetornoAlmoco
    BEGIN
    	SET @iAlmoco = DATEDIFF(MINUTE, @tSaidaAlmoco, @tRetornoAlmoco)
    /*	PRINT('ALMOCO: ' + CONVERT(VARCHAR,@iAlmoco)) */
    END
    
    WHILE DATEPART(DW, @dInicial) IN (1,7) AND @dInicial < @dFinal
    BEGIN
    	SET @dInicial = CONVERT(DATETIME, CONVERT(VARCHAR, @dInicial + 1, 112)) + CONVERT(VARCHAR, (REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEPART(HOUR, @tHoraEntrada)))) + CONVERT(VARCHAR, DATEPART(HOUR, @tHoraEntrada))) + ':' + (REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEPART(MINUTE, @tHoraEntrada)))) + CONVERT(VARCHAR, DATEPART(MINUTE, @tHoraEntrada))))
    /*	PRINT ('D.INICIAL + 1º DIA UTIL: ' + CONVERT(VARCHAR, @dInicial)) */
    END
    
    WHILE DATEPART(DW, @dFinal) IN (1,7) AND @dInicial < @dFinal
    BEGIN
    	SET @dFinal = CONVERT(DATETIME, CONVERT(VARCHAR, @dFinal + 1, 112)) +  CONVERT(VARCHAR, (REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEPART(HOUR, @tHoraSaida)))) + CONVERT(VARCHAR, DATEPART(HOUR, @tHoraSaida))) + ':' + (REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEPART(MINUTE, @tHoraSaida)))) + CONVERT(VARCHAR, DATEPART(MINUTE, @tHoraSaida))))
    /*	PRINT ('D.FINAL + 1º DIA UTIL: ' + CONVERT(VARCHAR, @dFinal)) */
    END
    
    SET @dInicialTemp = CONVERT(DATETIME, CONVERT(VARCHAR, @dInicial, 112)) + CONVERT(VARCHAR, (REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEPART(HOUR, @tHoraEntrada)))) + CONVERT(VARCHAR, DATEPART(HOUR, @tHoraEntrada))) + ':' + (REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEPART(MINUTE, @tHoraEntrada)))) + CONVERT(VARCHAR, DATEPART(MINUTE, @tHoraEntrada))))
    /*PRINT ('D.INICIAL TEMP: ' + CONVERT(VARCHAR, @dInicialTemp)) */
    
    SET @dFinalTemp   = CONVERT(DATETIME, CONVERT(VARCHAR, @dFinal, 112)) + CONVERT(VARCHAR, (REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEPART(HOUR, @tHoraEntrada)))) + CONVERT(VARCHAR, DATEPART(HOUR, @tHoraEntrada))) + ':' + (REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEPART(MINUTE, @tHoraEntrada)))) + CONVERT(VARCHAR, DATEPART(MINUTE, @tHoraEntrada))))
    /*PRINT ('D.FINAL TEMP: ' + CONVERT(VARCHAR, @dFinalTemp)) */
    
    SET @iMinutosUteisDia = DATEDIFF(MINUTE, @tHoraEntrada, @tSaidaAlmoco) + DATEDIFF(MINUTE, @tRetornoAlmoco, @tHoraSaida)
    /*PRINT('MINUTOS UTEIS: ' + CONVERT(VARCHAR, @iMinutosUteisDia)) */
    
    SET @iMinutosTotaisDia = DATEDIFF(MINUTE, @tHoraEntrada, @tHoraSaida)
    /*PRINT('TOTAL MINUTOS DIA: ' + CONVERT(VARCHAR, @iMinutosTotaisDia)) */
    
    SET @iMinutos = DATEDIFF(DD, @dInicial, @dFinal) * @iMinutosUteisDia +((SELECT CASE	WHEN DATEDIFF(MI, @dInicialTemp, @dInicial) < 0 THEN 0
    																					WHEN DATEDIFF(MI, @dInicialTemp, @dInicial) > @iMinutosTotaisDia THEN @iMinutosTotaisDia
    																					ELSE DATEDIFF(MI, @dInicialTemp, @dInicial) END) * -1 +
    																	   (SELECT CASE WHEN DATEDIFF(MI, @dFinalTemp, @dFinal) < 0 THEN 0
    																					WHEN DATEDIFF(MI, @dFinalTemp, @dFinal) > @iMinutosTotaisDia THEN @iMinutosTotaisDia
    																					ELSE DATEDIFF(MI, @dFinalTemp, @dFinal) END)) +
    																	   (SELECT CASE WHEN CAST(@dInicial AS TIME) >= @tRetornoAlmoco AND CAST(@dInicial AS TIME) <> CAST(@dFinal AS TIME) THEN @iMinutosTotaisDia - @iMinutosUteisDia 
    																					ELSE 0 END) -
    																	   (SELECT CASE WHEN CAST(@dFinal AS TIME) >= @tSaidaAlmoco AND CAST(@dFinal AS TIME) <= @tRetornoAlmoco THEN @iMinutosTotaisDia - @iMinutosUteisDia 
    																					ELSE 0 END) 
    
    /*PRINT(CONVERT(VARCHAR, ((DATEDIFF(DD, @dInicial, @dFinal) * @iMinutosUteisDia) +(@iTEMP * -1 + @iTEMP2)))) */
    /*PRINT('DIAS ENTRE INI & FIM * MINUTOS UTEIS: ' + CONVERT(VARCHAR, DATEDIFF(DD, @dInicial, @dFinal) * @iMinutosUteisDia)) */
    /*PRINT('MINUTOS ENTRE INI.TEMP & INI: ' + CONVERT(VARCHAR, DATEDIFF(MI, @dInicialTemp, @dInicial))) */
    /*PRINT('MINUTOS ENTRE FIM.TEMP & FIM: ' + CONVERT(VARCHAR, DATEDIFF(MI, @dFinalTemp, @dFinal))) */
    /*PRINT('MINUTOS: ' + CONVERT(VARCHAR, @iMinutos)) */
    
    WHILE @dInicial < = @dFinal
    BEGIN
    	IF DATEPART(DW, @dInicial) IN (1,7)
    	BEGIN
    		SET @iDiasInuteis = @iDiasInuteis + 1
    	END
    	SET @dInicial = @dInicial + 1
    END
    /*PRINT('DIAS INUTEIS: ' + CONVERT(VARCHAR, @iDiasInuteis)) */
    
    SET @iMinutos = (SELECT CASE WHEN @iMinutos < (@iDiasInuteis * @iMinutosTotaisDia) THEN 0
    							 ELSE @iMinutos - (@iDiasInuteis * @iMinutosUteisDia) END)
    /*PRINT('MINUTOS - DIAS INUTEIS: ' + CONVERT(VARCHAR, @iMinutos)) */
    
    SET @iMinutos = @iMinutos - @iAlmoco
    /*PRINT('ALMOCO: ' + CONVERT(VARCHAR, @iAlmoco)) */
    /*PRINT('MINUTOS - ALMOCO: ' + CONVERT(VARCHAR, @iMinutos)) */
    
    /*PRINT('CONVERSAO DE MINUTOS EM HORAS: ' + CONVERT(VARCHAR, CONVERT(int, @iMinutos / 60)) + ':' + RIGHT(CONVERT(VARCHAR, CONVERT(int, @iMinutos % 60) + 100), 2) ) */
    SELECT(CONVERT(VARCHAR, CONVERT(int, @iMinutos / 60)) + ':' + RIGHT(CONVERT(VARCHAR, CONVERT(int, @iMinutos % 60) + 100), 2) ) AS 'CONVERSAO DE MINUTOS EM HORAS:'

    quarta-feira, 16 de maio de 2012 14:32
  • Olá Savoine,

    obrigado por compartilhar


    Att.
    Lukas Baldan

    quarta-feira, 16 de maio de 2012 15:26
  • Savoine e Lucas,

    Achei excelente o script, porém, quando colocamos datas diferentes, ele computa uma hora a mais.

    Exemplo:

    SET @dInicial       = '2014-04-21 16:00:00'
    SET @dFinal         = '2014-04-22 18:00:00'
    SET @tHoraEntrada   = '09:00'
    SET @tSaidaAlmoco   = '12:00'
    SET @tRetornoAlmoco = '13:00'
    SET @tHoraSaida    = '18:00'

    Neste caso, deveriam ser 2 hrs do dia 21 mais 8hrs do dia 22, então seria um total de 10hrs, todavia, o script informa 11hrs. Como resolver?

    • Sugerido como Resposta RickkBr terça-feira, 22 de abril de 2014 14:37
    • Editado RickkBr terça-feira, 22 de abril de 2014 14:43
    • Não Sugerido como Resposta RickkBr terça-feira, 22 de abril de 2014 14:43
    terça-feira, 22 de abril de 2014 14:03
  • -- Consegui, vejam:

    DECLARE @DtHrAbertura DATETIME, @DtHrFechamento DATETIME, @HrEntrada TIME, @HrSaida TIME, @HrInicioAlmoco TIME, @HrFimAlmoco TIME SELECT @DtHrAbertura = '2014-04-21 12:00:00', @DtHrFechamento = '2014-04-22 17:30:00', @HrEntrada = '09:00:00', @HrSaida = '18:00:00', @HrInicioAlmoco = '12:00:00', @HrFimAlmoco = '13:00:00' -- Popular tabela temporária para filtrar o resultado DECLARE @tbTempoAtendimento AS TABLE (Data DATETIME) WHILE @DtHrAbertura <= @DtHrFechamento BEGIN IF ( CAST(@DtHrAbertura AS TIME) BETWEEN @HrInicioAlmoco AND @HrFimAlmoco -- Ignorar Horario de Almoço OR DATEPART(WEEKDAY,@DtHrAbertura) in (7,1) -- Ignorar Sabado e Domingo OR CAST(@DtHrAbertura AS TIME) NOT BETWEEN @HrEntrada AND @HrSaida -- Ignorar Horarios Fora de Expediente ) SET @DtHrAbertura = DATEADD(MINUTE, 1, @DtHrAbertura) ELSE BEGIN INSERT @tbTempoAtendimento SELECT @DtHrAbertura SET @DtHrAbertura = DATEADD(MINUTE, 1, @DtHrAbertura) END END -- Resultado Total de Hrs trabalhadas SELECT HrsTrabalhadas = DATEADD(mi, (COUNT(Data)/Cast(60 as decimal(4,2)) - FLOOR(COUNT(Data)/Cast(60 as decimal(4,2)))) * 60, DATEADD(hh, FLOOR(COUNT(Data)/Cast(60 as decimal(4,2))), CAST ('00:00:00' AS TIME))) FROM @tbTempoAtendimento -- Resultado por Dia de Hrs trabalhadas SELECT [Dia] = CASE DATEPART(WEEKDAY,CAST(Data AS DATE)) WHEN 1 THEN 'Domingo' WHEN 2 THEN 'Segunda-feira' WHEN 3 THEN 'Terça-feira' WHEN 4 THEN 'Quarta-feira' WHEN 5 THEN 'Quinta-feira' WHEN 6 THEN 'Sexta-feira' WHEN 7 THEN 'Sábado' END, [Entrada] = MIN(Data), [Saída] = MAX(Data), [HrsTrabalhadas] = DATEADD(mi, (COUNT(Data)/Cast(60 as decimal(4,2)) - FLOOR(COUNT(Data)/Cast(60 as decimal(4,2)))) * 60, DATEADD(hh, FLOOR(COUNT(Data)/Cast(60 as decimal(4,2))), CAST ('00:00:00' AS TIME))) FROM @tbTempoAtendimento GROUP BY CAST(Data AS DATE)




    • Editado RickkBr terça-feira, 22 de abril de 2014 14:58
    • Sugerido como Resposta RickkBr terça-feira, 22 de abril de 2014 14:58
    terça-feira, 22 de abril de 2014 14:56