Usuário com melhor resposta
Datediff - tirar fim-de-semana

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.
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
-
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
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 -
/* 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
-
-
-
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
-
-
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);- Editado João Paulo DMF sexta-feira, 23 de outubro de 2015 19:32