none
Datediff - tirar fim-de-semana RRS feed

  • Pergunta

  • Boa tarde a todos,

    Preciso fazer a diferença entre duas datas, porém quero contar as horas até Sexta-Feira às 17hs e depois iniciar a contagem apenas na Segunda-Feira às 8hs.
    Exemplo:

    data_entrada
    2009-10-02 10:00:00 (sexta-feira)

    data_saida
    2009-10-05 11:00:00 (segunda-feira)

    Com datediff em horas tenho 73hs, contando até sexta às 17hs e depois a partir de segunda às 8hs, teria um total de 10hs, alguém sabe uma maneira de fazer essa lógica?

    O problema é que eu nunca sei a data de entrada e a data de saída, pode ter mais de um fim-de-semana no meio.

    Desde já agradeço, Tatiana.




    segunda-feira, 19 de outubro de 2009 19:24

Respostas

  • /*
        Olá Tatiana, segue uma inspiração p/ sua dúvida
    
    obs.: Sempre q possível informe a versão do sql.
    (pois eu poderia utilizar uma CTE para ilustrar o exemplo)
    
    */
    
    DECLARE @DtEntrada   DATETIME, 
            @DtSaida     DATETIME,
            @HoraEntrada TIME    , 
            @HoraSaida   TIME  
    
    
    SELECT @DtEntrada   = '2009-10-02 10:00:00', 
           @DtSaida     = '2009-10-05 11:00:00 ',
           @HoraEntrada = '08:00:00', 
           @HoraSaida   = '17:00:00'
    
    
    -- Populo a tabela para filtrar o resultado
    DECLARE @tbPonto AS TABLE (Data DATETIME)
    
    SET NOCOUNT ON
    WHILE @DtEntrada <= @DtSaida BEGIN
    	INSERT @tbPonto SELECT @DtEntrada
    	SET @DtEntrada = DATEADD(HOUR, 1, @DtEntrada) 
    END
    
    -- Resultado Total de horas trabalhadas
    SELECT  
    	[Entrada] = MIN(Data),
    	[Saída]   = MAX(Data),
    	[Horas Trabalhadas] = CAST(COUNT(Data) -1 AS VARCHAR(3)) + 'hs'
    FROM 
    	@tbPonto 
    WHERE 
    	DATEPART(WEEKDAY,Data) NOT IN (7,1)
    AND CAST(DATA AS TIME) BETWEEN @HoraEntrada AND @HoraSaida
    
    
    -- Resultado por Dia de horas 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),
    	[Horas Trabalhadas] = CAST(COUNT(Data) - 1 AS VARCHAR(3)) + 'hs'
    FROM 
    	@tbPonto 
    WHERE 
    	DATEPART(WEEKDAY,Data) NOT IN (7,1)
    AND CAST(DATA AS TIME) BETWEEN @HoraEntrada and @HoraSaida
    GROUP BY
    	CAST(Data AS DATE)
    	
    	
    -- Espero ter te ajudado, se for útil, por favor classifique o post.	

    att. Leonardo Marcelino
    • Sugerido como Resposta Garcia, Marcelo terça-feira, 20 de outubro de 2009 11:26
    • Marcado como Resposta Tatiana_R_M terça-feira, 20 de outubro de 2009 18:47
    segunda-feira, 19 de outubro de 2009 23:55
  • Tatiana, o Leonardo já postou uma solução totalmente funcional, mas lembrei de um outro metodo.

    Aproveitando uma query que utilizamos aqui, que é bem mais complexa que está que estou postanto, pois utiliza tambem uma tabela de feriados e o calculo de feriados móveis.
    Eu tentei simplificar e talvez ajude:
    A grosso modo ela conta quantos dias (por dia da semana) há entre o período, faço a qtd de dias que me interessam (Segunda a Sexta) vezes a quantidade de horas úteis por dia.
    Em seguida, retiro a diferença de horas entre a entrada e saida x horario comercial

    A grande vantagem é não ter loop.

    A query é um pouco complexa, utiliza uma CTE com 3 querys e recursividade, fora uma pivot table.

    declare @HrComercialInicio datetime, @HrComercialFim datetime
    Set @HrComercialInicio = Convert(DateTime, '08:00', 103)
    Set @HrComercialFim = Convert(DateTime, '17:00', 103)

    declare @dtInicio datetime ,@dtFim datetime
    Set @dtInicio =  Convert(DateTime, '02/10/2009 10:00:00', 103)
    Set @dtFim = Convert(DateTime, '05/10/2009 11:00:00', 103)

    ;with DaysWeek (cDate, DayWeek, DayCount) as
    (
      Select @dtInicio,
             DatePart(weekday, @dtInicio),
             1 DayCount
      Union All
      Select
             dateAdd(day, 1, A.cDate),
             DatePart(weekday, dateAdd(day, 1, A.cDate)),
             1 DayCount
       from daysWeek A
      where cDate + 1 <= @dtFim),
    CountDaysWeekHoriz as (Select DayWeek, Count(DayCount) as Qtd from DaysWeek Group by DayWeek ),
    CountDaysWeek as ( Select IsNull([1], 0) as Domingo,
             IsNull([2], 0) as Segunda,
             IsNull([3], 0) as Terca,
             IsNull([4], 0) as Quarta,
             IsNull([5], 0) as Quinta,
             IsNull([6], 0) as Sexta,
             IsNull([7], 0) as Sabado
           from CountDaysWeekHoriz
         Pivot (Sum(Qtd) for DayWeek in ([1], [2], [3], [4], [5], [6], [7])) as z)
    Select (Segunda + Terca + Quarta + Quinta + Sexta) * (DateDiff(hh, @HrComercialInicio, @HrComercialFim))
            -
           DatePart(hh, (Cast(Convert(VarChar, @dtInicio, 108) as DateTime) - @HrComercialInicio)
           +
           (@HrComercialFim - Cast(Convert(VarChar, @dtFim, 108) as DateTime)))
      from CountDaysWeek


     


    Tks. Fausto Fiorese Branco DBA - SQL Server 2k5 São Paulo - Brasil * http://dba-sqlserver.blogspot.com/
    • Marcado como Resposta Tatiana_R_M terça-feira, 20 de outubro de 2009 18:48
    terça-feira, 20 de outubro de 2009 16:23

Todas as Respostas

  • Olá Tatiana,

    Você poderia explicar melhor o que realmente você quer?

    Estarei sempre a sua disposição para melhor lhe atender.

    Caso tenha lhe ajudado não se esqueça de marca como útil, só assim ajudará a melhorar a qualidade do fórum.

    Heberton Melo
    MCP | MCTS em SQL Server 2008 | Projetista de Dados

    Blog: http://heberton-melo.spaces.live.com

    segunda-feira, 19 de outubro de 2009 21:02
  • /*
        Olá Tatiana, segue uma inspiração p/ sua dúvida
    
    obs.: Sempre q possível informe a versão do sql.
    (pois eu poderia utilizar uma CTE para ilustrar o exemplo)
    
    */
    
    DECLARE @DtEntrada   DATETIME, 
            @DtSaida     DATETIME,
            @HoraEntrada TIME    , 
            @HoraSaida   TIME  
    
    
    SELECT @DtEntrada   = '2009-10-02 10:00:00', 
           @DtSaida     = '2009-10-05 11:00:00 ',
           @HoraEntrada = '08:00:00', 
           @HoraSaida   = '17:00:00'
    
    
    -- Populo a tabela para filtrar o resultado
    DECLARE @tbPonto AS TABLE (Data DATETIME)
    
    SET NOCOUNT ON
    WHILE @DtEntrada <= @DtSaida BEGIN
    	INSERT @tbPonto SELECT @DtEntrada
    	SET @DtEntrada = DATEADD(HOUR, 1, @DtEntrada) 
    END
    
    -- Resultado Total de horas trabalhadas
    SELECT  
    	[Entrada] = MIN(Data),
    	[Saída]   = MAX(Data),
    	[Horas Trabalhadas] = CAST(COUNT(Data) -1 AS VARCHAR(3)) + 'hs'
    FROM 
    	@tbPonto 
    WHERE 
    	DATEPART(WEEKDAY,Data) NOT IN (7,1)
    AND CAST(DATA AS TIME) BETWEEN @HoraEntrada AND @HoraSaida
    
    
    -- Resultado por Dia de horas 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),
    	[Horas Trabalhadas] = CAST(COUNT(Data) - 1 AS VARCHAR(3)) + 'hs'
    FROM 
    	@tbPonto 
    WHERE 
    	DATEPART(WEEKDAY,Data) NOT IN (7,1)
    AND CAST(DATA AS TIME) BETWEEN @HoraEntrada and @HoraSaida
    GROUP BY
    	CAST(Data AS DATE)
    	
    	
    -- Espero ter te ajudado, se for útil, por favor classifique o post.	

    att. Leonardo Marcelino
    • Sugerido como Resposta Garcia, Marcelo terça-feira, 20 de outubro de 2009 11:26
    • Marcado como Resposta Tatiana_R_M terça-feira, 20 de outubro de 2009 18:47
    segunda-feira, 19 de outubro de 2009 23:55
  • Leonardo,

    Parabéns pelo código, quando fui pensar em criar um exemplo, você já tinha postado.
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    terça-feira, 20 de outubro de 2009 00:18
  • valeu Junior,

    Tenho aprendido muita coisa acompanhado o forum.
     
    att. Leonardo Marcelino
    terça-feira, 20 de outubro de 2009 01:34
  • Tatiana, o Leonardo já postou uma solução totalmente funcional, mas lembrei de um outro metodo.

    Aproveitando uma query que utilizamos aqui, que é bem mais complexa que está que estou postanto, pois utiliza tambem uma tabela de feriados e o calculo de feriados móveis.
    Eu tentei simplificar e talvez ajude:
    A grosso modo ela conta quantos dias (por dia da semana) há entre o período, faço a qtd de dias que me interessam (Segunda a Sexta) vezes a quantidade de horas úteis por dia.
    Em seguida, retiro a diferença de horas entre a entrada e saida x horario comercial

    A grande vantagem é não ter loop.

    A query é um pouco complexa, utiliza uma CTE com 3 querys e recursividade, fora uma pivot table.

    declare @HrComercialInicio datetime, @HrComercialFim datetime
    Set @HrComercialInicio = Convert(DateTime, '08:00', 103)
    Set @HrComercialFim = Convert(DateTime, '17:00', 103)

    declare @dtInicio datetime ,@dtFim datetime
    Set @dtInicio =  Convert(DateTime, '02/10/2009 10:00:00', 103)
    Set @dtFim = Convert(DateTime, '05/10/2009 11:00:00', 103)

    ;with DaysWeek (cDate, DayWeek, DayCount) as
    (
      Select @dtInicio,
             DatePart(weekday, @dtInicio),
             1 DayCount
      Union All
      Select
             dateAdd(day, 1, A.cDate),
             DatePart(weekday, dateAdd(day, 1, A.cDate)),
             1 DayCount
       from daysWeek A
      where cDate + 1 <= @dtFim),
    CountDaysWeekHoriz as (Select DayWeek, Count(DayCount) as Qtd from DaysWeek Group by DayWeek ),
    CountDaysWeek as ( Select IsNull([1], 0) as Domingo,
             IsNull([2], 0) as Segunda,
             IsNull([3], 0) as Terca,
             IsNull([4], 0) as Quarta,
             IsNull([5], 0) as Quinta,
             IsNull([6], 0) as Sexta,
             IsNull([7], 0) as Sabado
           from CountDaysWeekHoriz
         Pivot (Sum(Qtd) for DayWeek in ([1], [2], [3], [4], [5], [6], [7])) as z)
    Select (Segunda + Terca + Quarta + Quinta + Sexta) * (DateDiff(hh, @HrComercialInicio, @HrComercialFim))
            -
           DatePart(hh, (Cast(Convert(VarChar, @dtInicio, 108) as DateTime) - @HrComercialInicio)
           +
           (@HrComercialFim - Cast(Convert(VarChar, @dtFim, 108) as DateTime)))
      from CountDaysWeek


     


    Tks. Fausto Fiorese Branco DBA - SQL Server 2k5 São Paulo - Brasil * http://dba-sqlserver.blogspot.com/
    • Marcado como Resposta Tatiana_R_M terça-feira, 20 de outubro de 2009 18:48
    terça-feira, 20 de outubro de 2009 16:23
  • Muito obrigada, estou adaptando o código para meu banco, ambas as respostas me ajudaram bastante.
    terça-feira, 20 de outubro de 2009 19:27
  • Achei esse código no stackoverflow, apenas verifique as datas e se o domingo e sábado são mesmo os dias 6 e 7 da semana

    SET DATEFIRST 1;
    DECLARE @StartDate DATETIME = '20131103',
           
    @EndDate DATETIME = '20131104';

    -- GENERATE A LIST OF ALL DATES BETWEEN THE START DATE AND THE END DATE
    WITH AllDates AS
    (   SELECT  TOP (DATEDIFF(DAY, @StartDate, @EndDate))
                D
    = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), @StartDate)
       
    FROM    sys.all_objects a
               
    CROSS JOIN sys.all_objects b
    )
    SELECT  WeekDays = COUNT(*)
    FROM    AllDates
    WHERE   DATEPART(WEEKDAY, D) NOT IN (6, 7);


    sexta-feira, 23 de outubro de 2015 19:30