Calculo com horas

Question

Pessoal boa tarde! Tudo bem?

Estou enfrentando algumas dificuldades para fazer um cálculo de horas. Preciso fazer um cálculo de horas trabalhadas de por um funcionário por dia (Folha de ponto).

O que acontece é que existem valores tem dias que dão certo e dias que não dão, além de possuir um agravante que seria o horario noturno.

Porém como o exemplo abaixo.:

vários dias e estão com os valores incorretos.

Segue abaixo meu código.:

select
	codfun, datpon, convert(varchar, datpon, 103) as dia, entr01, said01, entr02, said02,
	case
		when convert(int, substring(said01, 1, 2)) > convert(int, substring(entr01, 1, 2))
			then abs(sum(datediff(second, convert(time, said01), convert(time, entr01))) / 60 / 60)
		else
			abs(sum(datediff(second, convert(time, entr01), convert(time, '23:59'))) / 60 / 60) + 
			abs(sum(datediff(second, convert(time, '00:01'), convert(time, said01))) / 60 / 60)
	end + 
	case
		when convert(int, substring(said02, 1, 2)) > convert(int, substring(entr02, 1, 2))
			then abs(sum(datediff(second, convert(time, entr02), convert(time, said02))) / 60 / 60)
		else
			abs(sum(datediff(second, convert(time, entr02), convert(time, '23:59'))) / 60 / 60) + 
			abs(sum(datediff(second, convert(time, '00:01'), convert(time, said02))) / 60 / 60)
	end as Horas, 
	case
		when convert(int, substring(said01, 1, 2)) > convert(int, substring(entr01, 1, 2))
			then abs(sum(datediff(second, convert(time, said01), convert(time, entr01))) / 60 % 60)
		else
			abs(sum(datediff(second, convert(time, entr01), convert(time, '23:59'))) / 60 % 60) + 
			abs(sum(datediff(second, convert(time, '00:01'), convert(time, said01))) / 60 % 60)
	end + 
	case
		when convert(int, substring(said02, 1, 2)) > convert(int, substring(entr02, 1, 2))
			then abs(sum(datediff(second, convert(time, entr02), convert(time, said02))) / 60 % 60)
		else
			abs(sum(datediff(second, convert(time, entr02), convert(time, '23:59'))) / 60 % 60) + 
			abs(sum(datediff(second, convert(time, '00:01'), convert(time, said02))) / 60 % 60)
	end as Minutos
from
	Arq255
where codfun = 14
	and datpon >= '20210201'
group by codfun, datpon, entr01, said01, entr02, said02
go

Alguém poderia me ajudar com alguma sugestão.

Meu muito obrigado desde já!

Answer 1

Rafa_060990,

Por acaso estes dias que tem valores incorretos, seriam dias em que a hora começou em um dia e terminou no outro?

Veja se este exemplo te ajuda:

CREATE TABLE RESP1
(
	ID INT,
	DATA DATE,
	HORA TIME
)
Go

INSERT INTO [RESP1] ([ID],[DATA],[HORA]) VALUES(145,Getdate(),'08:01:00')
INSERT INTO [RESP1] ([ID],[DATA],[HORA]) VALUES(145,Getdate(),'12:08:00')
INSERT INTO [RESP1] ([ID],[DATA],[HORA]) VALUES(145,Getdate(),'13:16:00')
INSERT INTO [RESP1] ([ID],[DATA],[HORA]) VALUES(145,Getdate(),'17:02:00')

INSERT INTO [RESP1] ([ID],[DATA],[HORA]) VALUES(145,Getdate()+1,'08:00:00')
INSERT INTO [RESP1] ([ID],[DATA],[HORA]) VALUES(145,Getdate()+1,'12:00:00')
INSERT INTO [RESP1] ([ID],[DATA],[HORA]) VALUES(145,Getdate()+1,'13:00:00')
INSERT INTO [RESP1] ([ID],[DATA],[HORA]) VALUES(145,Getdate()+1,'17:00:00')
Go

;WITH LANCA AS (
SELECT ID,DATA,HORA,
ROW_NUMBER() OVER (
PARTITION BY DATA,ID ORDER BY DATA,ID) AS POS
FROM RESP1
),
LANCAORG AS(
SELECT  L1.ID,L1.DATA, L1.HORA AS ENTRADA, L2.HORA AS SAIDA
FROM LANCA AS L1
INNER JOIN LANCA AS L2 ON
L1.ID = L2.ID AND L1.DATA = L2.DATA AND L1.POS = L2.POS - 1 AND L1.POS %2=1)

SELECT ID, DATA,
    RIGHT('0' + CAST(SUM(DateDiff(Mi,Entrada,Saida)) / 60 As VARCHAR(2)),2) + ':' +
    RIGHT('0' + CAST(SUM(DateDiff(Mi,Entrada,Saida)) % 60 As VARCHAR(2)),2)
As CargaHoraria
FROM LANCAORG GROUP BY ID, Data
Go

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 2

Bom dia Junior! Tudo bem?

Desde já meu muito obrigado pela resposta.

Então, mudei meu código baseado no seu exemplo, e sim, deu certo quando o horário é diurno. Porém no noturno, quando tem a virada não dá certo o cálculo.

Em minha base de dados, os valores não vem como um dia a frente, eles vem no mesmo dia, porém não bate os valores. Seguem resultados e o novo código.

;with cte_Diferencas as
(
select
	codfun, datpon,
	entr01, said01, entr02, said02,
	entr03, said03, entr04, said04,

	case
		when convert(int, substring(said01, 1, 2)) > convert(int, substring(entr01, 1, 2))
			then datediff(mi, convert(time, said01), convert(time, entr01))
		else
			datediff(mi, convert(time, entr01), convert(time, '23:59')) + 
			datediff(mi, convert(time, '00:01'), convert(time, said01))
	end as Dif1,

	case
		when convert(int, substring(said02, 1, 2)) > convert(int, substring(entr02, 1, 2))
			then datediff(mi, convert(time, said02), convert(time, entr02))
		else
			datediff(mi, convert(time, entr02), convert(time, '23:59')) + 
			datediff(mi, convert(time, '00:01'), convert(time, said02))
	end as Dif2,

	case
		when convert(int, substring(said03, 1, 2)) > convert(int, substring(entr03, 1, 2))
			then datediff(mi, convert(time, said03), convert(time, entr03))
		else
			datediff(mi, convert(time, entr03), convert(time, '23:59')) + 
			datediff(mi, convert(time, '00:01'), convert(time, said03))
	end as Dif3,

	case
		when convert(int, substring(said04, 1, 2)) > convert(int, substring(entr04, 1, 2))
			then datediff(mi, convert(time, said04), convert(time, entr04))
		else
			datediff(mi, convert(time, entr04), convert(time, '23:59')) + 
			datediff(mi, convert(time, '00:01'), convert(time, said04))
	end as Dif4
from
	Arq255
),

cte_Total as 
(
select
	codfun, datpon, sum(Dif1 + Dif2 + Dif3 + Dif4) As TotalMinutos
from
	cte_Diferencas
group by codfun, datpon
)

select
	cte_Total.codfun, cte_Total.datpon,
	Arq255.entr01, Arq255.said01, Arq255.entr02, Arq255.said02,
	Arq255.entr03, Arq255.said03, Arq255.entr04, Arq255.said04,
    cast((TotalMinutos / 60) as varchar(2)) + ':' +
    cast((TotalMinutos % 60) as varchar(2)) As CargaHoraria
from
	cte_Total
left outer join Arq255 on cte_Total.codfun = Arq255.codfun
	and cte_Total.datpon = Arq255.datpon
where cte_Total.codfun = 14
	and cte_Total.datpon >= '20210201'
go

Alguma sugestão de como posso resolver?

Obrigado!

Answer 3

rafa,

Não estou entendendo o porquê você esta convertendo os dados?

Qual é o tipo de dados que você esta utilizando para as colunas que vão armazenados os valores de datas?

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]