Cálculo de intervalo - Como fazer

Question

Pessoal, tenho uma dúvida grande e gostaria de compartilhar com vocês. Tenho um aplicativo que faz agendamentos de reuniões, advogados e etc... Bem, entre várias tabelas, destaco essas que são a minha dúvida(Agenda e Horário). Nessa tabela, agenda, eu cadastro a agenda do profissional. Nela tem dois campos importantes. Intervalo e Período. O Intervalo é o intervalo entre uma consulta e outra. Período é o período que a agenda estará disponível, podendo ser de um mês, uma semana, uma quinzena ou até trimestre, semestre e etc... Há outra tabela que é a tabela Horário. Essa tabela que está a dúvida e deveria fazer isso: Quando eu cadastro a agenda, ela dispara uma Stored Procedure, que faz a gravação na Tabela Agenda e Insere também na Tabela horario. A dúvida é a seguinte. Como eu faço para gerar autamaticamente, todos os horário obedecendo o Intervalo passado, populando a Tabela Horario, assim:

ID_PROF = 1, Intervalo = 45min, Periodo = 7 dias, Duracao_Agenda = 8 Horas, Hora_Inicio = 8:00, Hora_Fim = 18:00, ALMOCO = 1 hora.

Tendo esses dados acima, criar a tabela Horario, com intervalos de 45 min, exceto das 12:00 às 13:00, até às 18:00, começando hoje e terminando depois de amanhã(3 dias). Como eu faço esse cálculo. Espero ter sido explícito na explicação.

Obrigado e no aguardo.

Answer 1

pnet,

Talvez não seja a melhor solução, mas pensando rapidamente, uma ideia é criar 2 CTE's recursivas, uma para montar os horários da manhã e uma segunda, com os horários da tarde, usando a função dateadd até atingir o horário de parada e inserir esses horários na tabela.

Espero ter ajudado.

[]'s

Answer 2

Logan, como eu posso fazer isso? O que é CTE?

Answer 3

pnet,

CTE é a abreviação para "Common Table Expression". Pense nela como um resultset temporário, que roda somente no escopo da tua consulta... Maiores informações você encontra nesse link aqui: http://msdn.microsoft.com/pt-br/library/ms175972.aspx

Como você especifica nos teus parâmetros praticamente todos os horários, com exceção do horário de saída pra almoço, você deve assumir que será sempre meio dia.

Mas a ideia é a seguinte:

declare @inicio_manha datetime
declare @inicio_tarde datetime
declare @fim_tarde datetime
declare @intervalo smallint

set @inicio_manha = '2013-01-01 08:00:00'
set @intervalo = 45

; with CTE_Manha -- tabela recursiva das 08:00 as 12:00 com horários de 45 min...
as
(
  select id_prof, @inicio_manha as horario

  union all

  select id_prof, dateadd(MI,@intervalo,horario)
  from CTE_Manha
  WHERE dateadd(MI,@intervalo,horario) <= '2013-01-01 12:00:00'
)
insert into agenda (campo1, campo2, horário...)
select id_prof,campo1, campo2,horario
FROM ...
join CTE_Manha cte
on CTE.id_prof = tabela.id_prof

Aí você deve adaptar o insert acima para fazer o mesmo no final do dia, e executar isso para todos os dias que precisar.

Espero que te ajude...

Qualquer coisa, é só falar.

Answer 4

Pnet, 

Faltou uma informação: quanto tempo o advogado vai ficar alocado? E, derivada desta: existe um tempo mínimo e/ou máximo de alocação?

Eu resolvi um problema parecido, mas para escola. A questão era agendar sala x curso x professor x aluno x recursos (projetor, computador, etc.). O fator principal era criar os slots (horários+dia) e dai ser possível evitar conflitos (alunos querendo cursar dois cursos que colindem horário; professores alocados para cursos que colidem horário; projetor alocado para dois cursos no mesmo horário, etc.)

Se for possível criar esses slots, fica fácil. No seu caso se o tempo de consulta fosse 1h15m então você teria um slot a cada duas horas. Gerando ID para as horas+datas fica moleza.

Se não for possível, você vai precisar uns selects com between para achar conflitos.

[]s,

Armando Lacerda
/* Se a resposta foi útil, não esqueça de marcá-la */

---

Armando Lacerda

Answer 5

Achei interessante essa idéia de Slot. Poderia me dar uma luz a esse respeito? Meu problema todo está exatamente no Sql. Estou apanhando para fazer a agenda, para ir somando os intervalos às horas e obter um horário e ir fazendo até o fim do dia. O tempo de consulta é parametrizado. Pode ser de 45min, 60min ou mesmo 1:15.

Answer 6

pnet, a consulta que eu mostrei não ajudou em nada?

Faltou alguma coisa, ou está dando algum erro para você?

A consulta que eu mostrei realmente não valida conflitos de agenda, mas em princípio, pelo que você falou, não existiria conflito, uma vez que a agenda seria montada para a pessoa diretamente... Estou enganado?

Se pudermos ajudar em algo a mais, é só falar.

Answer 7

Pnet,

Parece que a menor granularidade de sua agenda é 15 minutos. Então lá vai minha sugestão:

1. Crie uma tabela para os slots de 15 minutos durante o seu dia comercial; algo assim:

Create Table TabelaDeHorarios (
   id uniqueidentifier default newid(),
   slotDate datetime not null,
   slotTime time not null
)
go

alter table TabelaDeHorarios add primary key (slotDate, slotTime)
go

2. Popule esta tabela com os horários da agenda; o script de exemplo abaixo cria 10.000 slots:

declare @slotDate datetime = '01/22/2013'
declare @slotTime datetime = '08:00:00'
declare @count int = 0

while (@count < 10000)
begin
   insert into TabelaDeHorarios (slotDate, slotTime) values (@slotDate, @slotTime)
   set @slotTime = DATEADD(MINUTE, 15, @slotTime)
   if (@slotTime = '18:00:00')
   begin
      set @slotDate = DATEADD(day, 1, @slotDate)
 set @slotTime = '08:00:00'
   end
   set @count += 1
end

3. Crie a tabela de agenda que conterá os slots agendados para os advogados, algo assim:

create table Agenda (
   id_Horario uniqueidentifier not null,
   id_advogado uniqueidentifier not null
)
go
alter table agenda add primary key (id_horario, id_advogado)
go

4. Tenha sua tabela com os advogados; a minha é simples assim:

Create Table Advogado (
   id uniqueidentifier default newid(),
   nome varchar(200)
)

5. Blackout as datas e horários indisponíveis; o script abaixo blackout o horário de almoço (não estou levando em consideração fim de semana, feriados, etc.): 

insert into Agenda (id_advogado, id_Horario)
select a.id, h.id
 from Advogado a
  cross join TabelaDeHorarios h
where h.slotTime between '12:00:00' and '12:45:00'

Pronto! Agora é só usar. :) 

Por exemplo, para agendar um advogado das 9h as 11h:

insert into Agenda (id_advogado, id_Horario) 
   select 'F3688A37-E12D-430C-9E73-A5935C8B90B4', -- id do advogado
          id -- id dos slots
     from TabelaDeHorarios
    where slotDate = '01/25/2013'
      and slotTime between '09:00:00' and '10:45:00'

o comando acima aloca 8 slots para esse compromisso. A query abaixo vai falhar porque vai violar a primary key da tabela de agenda, indicando que o compromisso iria colidir com outro pré-marcado:

insert into Agenda (id_advogado, id_Horario) 
   select 'F3688A37-E12D-430C-9E73-A5935C8B90B4', -- id do advogado
          id -- id dos slots
     from TabelaDeHorarios
    where slotDate = '01/25/2013'
      and slotTime between '10:00:00' and '11:30:00'

Como o comando está na mesma transação nenhum slot é alocado!

Como você pode ver, fica super fácil de alocar e previnir conflitos de agenda.

[]s,

/* Se a resposta foi útil, não esqueça de marcá-la */

---

Armando Lacerda