Help with handling employee schedules

Question

Help with handling employee schedules

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

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

sql sql-server

asked by anonymous 10.04.2018 / 18:29

2 answers

0

I've assembled an example by randomly selecting it. See if you can help:

;WITH grupoHorario AS
(
   SELECT almoco_saida, nome, ROW_NUMBER() OVER (PARTITION BY almoco_saida ORDER BY newid()) AS contadorGrupo FROM @func
)
SELECT grupoHorario.contadorGrupo, grupoHorario.nome, convert(varchar(30), almoco_saida,108) almoco_saida FROM grupoHorario WHERE contadorGrupo <= 5

11.04.2018 / 15:35

Cast to decimal in LINQ? Image shows a white border underneath

score 0 · Accepted Answer

My temporary solution was to make POG below, answer me for now but I think it can be improved or even have a new solution for the case.

declare @func as table (id int
                    , nome varchar(255) 
                    , h_entrada datetime 
                    , h_saida datetime
                    , almoco_saida datetime
                    , almoco_volta datetime
                    , prioridade int )

declare @cont int = 0 
      , @qtdNoMesmoHorario int = 4
      , @tempoDif datetime = '00:15:00'
      , @id int

-- =================================
-- funcionários
-- =================================
insert into @func(id, nome, h_entrada, almoco_saida, almoco_volta, h_saida, prioridade)
Values(1,'joao'      ,'09:00:00',NULL, NULL, '19:00:00', null),
      (2,'maria'     ,'08:00:00',NULL, NULL, '18:00:00', null),
      (3,'jose'      ,'08:30:00',NULL, NULL, '18:30:00', null),
      (4,'pedro'     ,'08:00:00',NULL, NULL, '18:00:00', null),
      (5,'thiago'    ,'08:00:00',NULL, NULL, '18:00:00', null),
      (6,'marcos'    ,'08:30:00',NULL, NULL, '18:30:00', null),
      (7,'mauricio'  ,'08:00:00',NULL, NULL, '18:00:00', null),
      (8,'bruna'     ,'09:00:00',NULL, NULL, '19:00:00', null),
      (9,'rita'      ,'08:30:00',NULL, NULL, '18:30:00', null),
      (10,'cassia'   ,'08:30:00',NULL, NULL, '18:30:00', null),
      (11,'nadia'    ,'08:30:00',NULL, NULL, '18:30:00', null),
      (12,'douglas'  ,'08:00:00',NULL, NULL, '18:00:00', null),
      (13,'debora'   ,'08:30:00',NULL, NULL, '18:30:00', null),
      (14,'guilherme','09:00:00',NULL, NULL, '19:00:00', null),
      (15,'victor'   ,'08:30:00',NULL, NULL, '18:30:00', null),
      (16,'frederico','08:30:00',NULL, NULL, '18:30:00', null),
      (17,'rafael'   ,'09:00:00',NULL, NULL, '19:00:00', null),
      (18,'geraldo'  ,'08:30:00',NULL, NULL, '18:30:00', null),
      (19,'edson'    ,'08:30:00',NULL, NULL, '18:30:00', null),
      (20,'vanessa'  ,'08:00:00',NULL, NULL, '18:00:00', null)

-- =================================
-- 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)
             , a.prioridade = case when convert(varchar(30),a.h_entrada,108) = '08:00:00'
                                   then 0
                                   when convert(varchar(30),a.h_entrada,108) = '08:30:00'
                                   then 1
                                   when convert(varchar(30),a.h_entrada,108) = '09:00:00'
                                   then 2 end
          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




-- =================================
-- separando o horario de saida
-- =================================
while exists (select top 1 1
                from @func  
               group by prioridade, almoco_saida 
              having count(*) > @qtdNoMesmoHorario )
begin

        select top 1 @id = id
          from @func a
         where a.almoco_saida = (select top 1 almoco_saida from @func group by prioridade, almoco_saida having count(*) > @qtdNoMesmoHorario order by prioridade, almoco_saida asc)
         order by a.prioridade asc, almoco_saida asc 

        -- somando mais 15min
        update a
           set a.almoco_saida = convert(varchar(30),(a.almoco_saida + convert(datetime,@tempoDif)),108)
             , a.almoco_volta = convert(varchar(30),(a.almoco_volta + convert(datetime,@tempoDif)),108)
          from @func a
         where a.id = @id

end



-- =================================
-- separando o horario de saida / p2
-- =================================
while exists (select top 1 1
                from @func  
               group by almoco_saida 
              having count(*) > @qtdNoMesmoHorario )
begin

        select top 1 @id = id
          from @func a
         where a.almoco_saida = (select top 1 almoco_saida from @func group by almoco_saida having count(*) > @qtdNoMesmoHorario order by almoco_saida desc)
         order by almoco_saida desc 

        -- somando mais 15min
        update a
           set a.almoco_saida = convert(varchar(30),(a.almoco_saida + convert(datetime,@tempoDif)),108)
             , a.almoco_volta = convert(varchar(30),(a.almoco_volta + convert(datetime,@tempoDif)),108)
          from @func a
         where a.id = @id

end




-- ========================
-- resultado final
-- ========================

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
     , ROW_NUMBER() OVER(partition by almoco_saida ORDER BY almoco_saida ASC) as qtdPorHorario
     , prioridade

  from @func
 order by prioridade, almoco_saida asc