Help with handling employees' schedules

Asked

Viewed 69 times

1

I have a situation regarding company employees' lunch check-in and check-out times; I need to limit the number of employees who can have lunch at the same time, at least together in the 15-minute break, so if I have 10 employees who would leave for lunch at 12:00, so only 5 can have lunch at that time and the other 5 can have lunch at 12:15 and so on.

The standard is that the departure for lunch happens 4 hours after the entrance of the employee and the return 5 hours after the entrance time, the departure for lunch can be extended up to 6 hours after the entrance time, already the entry and exit times do not suffer any change.

Each "class" can leave up to 5 employees at the same time. I did not find anything similar in Stack or even in Google, so anyone who has a link something similar or can help me in the query below, I thank.

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

  • If there are 15 employees to leave at 12:00, what is the criterion for selecting the first 5 (and successively)?

  • Hello @Josédiz, this criterion is defined by the company’s HR, what happens is that it can not have a greater number of employees than the capacity of the cafeteria, so in theory 15min and 5 at a time is the ideal flow for the entrance and exit of the cafeteria. The numbers are much bigger, I put these just as an example of the situation.

  • Another detail, in the example I have 6 employees who entered at 08:00, so they can have lunch between 12:00 and 14:00, it may be 12:00, 12:15, 12:30, 12:45... 13:45 and 14:00, while the 10 who entered at 08:30 may leave between 12:00:00 until 14:30, these groups can fit and can be reduced... in the example given to me 5 are leaving at 12:00 and 1 at 12:15, after that 5 are leaving at 13:00 and another 5 at 13:15 and last 4 are leaving at 14:00, the situation does not seem to be so complicated, but I can’t think of a solution to that.

  • @Ricardosouza what I answered didn’t help? Oce checked?

  • Hello @andreia_sp I checked, but does not answer me.

2 answers

0

I put together an example by selecting randomly. See if it helps you:

;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

  • Andreia, whenever there is doubt about the question, ask in the comments, as José Diz did. So both the question and the answer will be more complete. ;]

0


My interim solution was to POG below, suits me for now but I believe it can be improved or even have a new solution to 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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.