I have a situation regarding the lunchtime of the employees of the company; I need to limit the number of employees who can have lunch at the same time, at least together within 15 minutes, so if I have 10 employees leaving for lunch at 12:00, then only 5 can have lunch at that time, and the other 5 can eat lunch at 12:15 and so on.
The default is that the departure for lunch will occur 4 hours after the employee's entry and the return will be 5 hours after the check-in time, and the check-out for lunch may be extended until 6 hours after the check-in time. times of entry and exit are not changed.
Each "class" can leave up to 5 employees at the same time. I did not find anything similar in the Stack or even in Google, so whoever has a link of something similar or can help me in the query below, thank you.
declare @func as table (id int
, nome varchar(255)
, h_entrada datetime
, h_saida datetime
, almoco_saida datetime
, almoco_volta datetime)
declare @almoco datetime = '12:00:00'
, @cont int = 0
, @qtdNoMesmoHorario int = 5
, @tempoDif datetime = '00:15:00'
-- =================================
-- funcionários
-- =================================
insert into @func(id, nome, h_entrada, almoco_saida, almoco_volta, h_saida)
Values(1,'joao' ,'09:00:00',NULL, NULL, '19:00:00'),
(2,'maria' ,'08:00:00',NULL, NULL, '18:00:00'),
(3,'jose' ,'08:30:00',NULL, NULL, '18:30:00'),
(4,'pedro' ,'08:00:00',NULL, NULL, '18:00:00'),
(5,'thiago' ,'08:00:00',NULL, NULL, '18:00:00'),
(6,'marcos' ,'08:30:00',NULL, NULL, '18:30:00'),
(7,'mauricio' ,'08:00:00',NULL, NULL, '18:00:00'),
(8,'bruna' ,'09:00:00',NULL, NULL, '19:00:00'),
(9,'rita' ,'08:30:00',NULL, NULL, '18:30:00'),
(10,'cassia' ,'08:30:00',NULL, NULL, '18:30:00'),
(11,'nadia' ,'08:30:00',NULL, NULL, '18:30:00'),
(12,'douglas' ,'08:00:00',NULL, NULL, '18:00:00'),
(13,'debora' ,'08:30:00',NULL, NULL, '18:30:00'),
(14,'guilherme','09:00:00',NULL, NULL, '19:00:00'),
(15,'victor' ,'08:30:00',NULL, NULL, '18:30:00'),
(16,'frederico','08:30:00',NULL, NULL, '18:30:00'),
(17,'rafael' ,'09:00:00',NULL, NULL, '19:00:00'),
(18,'geraldo' ,'08:30:00',NULL, NULL, '18:30:00'),
(19,'edson' ,'08:30:00',NULL, NULL, '18:30:00'),
(20,'vanessa' ,'08:00:00',NULL, NULL, '18:00:00')
-- =================================
-- agrupando as qtd dos funcionários
-- =================================
select @cont = count(*) from @func
while (@cont >= 0)
begin
-- =================================
-- somando 4hs após a entrada
-- =================================
update a
set a.almoco_saida = convert(varchar(30),(a.h_entrada + convert(datetime,'04:00:00')),108)
, a.almoco_volta = convert(varchar(30),(a.h_entrada + convert(datetime,'05:00:00')),108)
from @func a
set @cont = @cont - 1
end
-- =================================
-- exibindo funcioários
-- =================================
select id
, nome
, convert(varchar(30),h_entrada,108) as h_entrada
, convert(varchar(30),almoco_saida,108) as almoco_saida
, convert(varchar(30),almoco_volta,108) as almoco_volta
, convert(varchar(30),h_saida,108) as h_saida
from @func
order by h_entrada asc
select count(*) as qtd
, convert(varchar(30),almoco_saida,108) as almoco_saida
from @func group by almoco_saida