none
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.

    Friday, January 18, 2013 12:08 PM

Answers

  • 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


    Tuesday, January 22, 2013 2:23 PM

All replies

  • 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

    Saturday, January 19, 2013 2:12 PM
  • Logan, como eu posso fazer isso? O que é CTE?
    Sunday, January 20, 2013 11:27 PM
  • 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.


    Monday, January 21, 2013 10:18 AM
  • 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

    Tuesday, January 22, 2013 4:55 AM
  • 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.
    Tuesday, January 22, 2013 1:00 PM
  • 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.

    Tuesday, January 22, 2013 1:24 PM
  • 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


    Tuesday, January 22, 2013 2:23 PM