none
Tabela temporária RRS feed

  • Pergunta

  • Olá,

     

    Com a ajuda dos colegas consegui chegar a esse modelo de script que está funcionando porém, eu preciso incluir na tabela temporaria o nome do dentista que está disponivel naquele horario.

     

    CREATE procedure pHoraMarcada
    (
           @IDataHora datetime,
           @FDataHora datetime
    )
    as
    declare @Dia        integer
    declare @Mes        integer
    declare @DiaMes varchar(5)
    begin
        create table #tbHoraMarcada -- tabela temporária
        (
               HoraConsulta  dateTime,
               DiaMes        varchar(5)

       )
        while(@IDataHora <= @FDataHora)
        begin
            set @IDataHora = dateadd(n,30,@IDataHora) -- o intervalo entre as consulta é de 30 minutos
            set @Dia = datepart(dd, @IDataHora)
            if(@Dia < 10)
              set @DiaMes = '0' + cast(@Dia as varchar) + '/'
            else
              set @DiaMes = cast(@Dia as varchar) + '/'
            set @Mes = datepart(mm, @IDataHora)
            if(@Mes < 10)
              set @DiaMes = @DiaMes + '0' + cast(@Mes as varchar)
            else
              set @DiaMes = cast(@DiaMes as varchar)
            insert into #tbHoraMarcada
            (
                   HoraConsulta,
                   DiaMes

           )
            values
            (
                   @IDataHora,
                   @DiaMes

           )
           
        end
        select #tbHoraMarcada.HoraConsulta, #tbHoraMarcada.DiaMes, Dentista,
               dbo.nDiaSemana(datepart(dw,HoraConsulta)) as DiaSemana
               from #tbHoraMarcada
               left outer join tbAgendamento on(tbAgendamento.DataHoraConsulta = #tbHoraMarcada.HoraConsulta)
               left outer join tbFeriado on(tbFeriado.DiaMes = #tbHoraMarcada.DiaMes)
        where
               tbAgendamento.DataHoraConsulta is null
        and
               datepart(dw,#tbHoraMarcada.HoraConsulta) between 2 and 6
        and
               tbFeriado.DiaMes is null
    end

     

    O script acima, já me traz os dados que preciso só não estou conseguindo incluir o nome do dentista.

     

    Nota: A tabela tbAgendamento, tem um campo chamado idDentista que se relaciona com o campo idDentista da tabela tbFuncionario.

     

    Alguma dica ?????

     

    Obrigado.

    segunda-feira, 11 de fevereiro de 2008 15:17

Todas as Respostas

  • Gilberto,

     

     

    Veja se este exemplo ajuda:

     

    CREATE procedure pHoraMarcada
    (
           @IDataHora datetime,
           @FDataHora datetime
    )
    as
    declare @Dia        integer
    declare @Mes        integer
    declare @DiaMes varchar(5)
    begin
        create table #tbHoraMarcada -- tabela temporária
        (
               HoraConsulta  dateTime,
               DiaMes        varchar(5)

       )
        while(@IDataHora <= @FDataHora)
        begin
            set @IDataHora = dateadd(n,30,@IDataHora) -- o intervalo entre as consulta é de 30 minutos
            set @Dia = datepart(dd, @IDataHora)
            if(@Dia < 10)
              set @DiaMes = '0' + cast(@Dia as varchar) + '/'
            else
              set @DiaMes = cast(@Dia as varchar) + '/'
            set @Mes = datepart(mm, @IDataHora)
            if(@Mes < 10)
              set @DiaMes = @DiaMes + '0' + cast(@Mes as varchar)
            else
              set @DiaMes = cast(@DiaMes as varchar)
            insert into #tbHoraMarcada
            (
                   HoraConsulta,
                   DiaMes

           )
            values
            (
                   @IDataHora,
                   @DiaMes

           )
           
        end
        select #tbHoraMarcada.HoraConsulta, #tbHoraMarcada.DiaMes, tf.Nome As Dentista,
               dbo.nDiaSemana(datepart(dw,HoraConsulta)) as DiaSemana
               from #tbHoraMarcada        
               left outer join tbAgendamento on(tbAgendamento.DataHoraConsulta = #tbHoraMarcada.HoraConsulta)

               left outer join tbFuncionario tf on tbAgendamento.IdDentista = tf.idDentista
               left outer join tbFeriado on(tbFeriado.DiaMes = #tbHoraMarcada.DiaMes)
        where
               tbAgendamento.DataHoraConsulta is null
        and
               datepart(dw,#tbHoraMarcada.HoraConsulta) between 2 and 6
        and
               tbFeriado.DiaMes is null
    end

    segunda-feira, 11 de fevereiro de 2008 15:40
  • Caro colega,

     

    Está devolvendo a coluna Dentista como null ao invés do nome do dentista.

     

    Obrigado.

    segunda-feira, 11 de fevereiro de 2008 15:59
  • Boa Tarde,

     

    Pelo que vejo, você está fazendo uma junção entre hora marcada com agendamento para chegar até o nome do dentista. Antes de prosseguir, algumas questões devem ser respondidas para garantir que a consulta está correta.

     

    - Toda "hora marcada" tem necessariamente um agendamento ?

    - Todo "agendamento" tem necessariamente um dentista (a coluna idDentista é NOT NULL) ?

     

    [ ]s,

     

    Gustavo

     

    segunda-feira, 11 de fevereiro de 2008 16:12
  • Caro colega,

     

    Sim....Nos dois casos, é obrigatório. E a coluna idDentista, não possuí null

     

    Obrigado.

    segunda-feira, 11 de fevereiro de 2008 16:18
  • Boa Tarde,

     

    Se isso é verdade, então por que utilizar o LEFT OUTER JOIN entre Hora Agendada e Agendamento ?

     

    [ ]s,

     

    Gustavo

     

    segunda-feira, 11 de fevereiro de 2008 16:47
  • Tudo bem.... Fiz um check list nas tabelas e os dados estão corretos inclusive já testei com inner join e nesse caso, ele não devolve nada.

     

    Obrigado.

    segunda-feira, 11 de fevereiro de 2008 17:07
  • Olá Gilberto,

     

    É que analisando o seguinte trecho:

     

    from #tbHoraMarcada
               left outer join tbAgendamento on(tbAgendamento.DataHoraConsulta = #tbHoraMarcada.HoraConsulta)
               left outer join tbFeriado on(tbFeriado.DiaMes = #tbHoraMarcada.DiaMes)

    Me parece que os registros em #tbHoraMarcada não tem necessariamente entradas em tbAgendamento e tbFeriado. Em relação a tbFeriado é compreensível e esperado, mas no caso de tbAgendamento, parece que nem toda hora marcada precisa ter um agendamento. Isso está correto ? Se sim, a consulta poderia ser substituído por

     

    from #tbHoraMarcada
               inner join tbAgendamento on(tbAgendamento.DataHoraConsulta = #tbHoraMarcada.HoraConsulta)
               left outer join tbFeriado on(tbFeriado.DiaMes = #tbHoraMarcada.DiaMes)

     

    Há divergência nos resultados se você testar esses dois códigos ?

     

    [ ]s,

     

    Gustavo

     

    segunda-feira, 11 de fevereiro de 2008 17:38
  • Então gustavo,

     

    Sim isso é verdadeiro....A questão é: Preciso inserir na tabela #tbHoraMarcada os intervalos de horas que não exista em agendamento.

     

    por ex.:

     

    na tabela tbAgendamento, tenho - 8:00, 8:30, 10:00, 11:00

    então preciso mostrar o que não está nela: 9:00, 9:30, 10:30 e assim sucessivamente.

     

    Com o script que passei inicialmente eu já consigo fazer isso só que agora eu preciso saber o nome dos dentista que estão disponiveis nesses horários que serão inseridos em #tbHoraMarcada(9:00, 9:30, 10:30 ).

     

    Com relação ao seu teste:

     

    from #tbHoraMarcada
               inner join tbAgendamento on(tbAgendamento.DataHoraConsulta = #tbHoraMarcada.HoraConsulta)
               left outer join tbFeriado on(tbFeriado.DiaMes = #tbHoraMarcada.DiaMes)

     

    não houver divergência ... os dois não exibe nada ou seja a tabela #tbOhoraMaracad deve branco.

     

    Obrigado.

    segunda-feira, 11 de fevereiro de 2008 17:47
  • Olá Gilberto,

     

    Desculpe se minhas perguntas pareceram repetitivas, mas agora ficou bem mais claro. Bom, se todo agendamento tem necessariamente um dentista (já que a coluna é NOT NULL) bastaria fazer a seguinte consulta:

     

    Code Snippet

    SELECT <Campos>

    from #tbHoraMarcada
               left outer join (tbAgendamento inner join tbFuncionario on tbAgendamento.idDentista = tbFuncionario.idFuncionario)

     on(tbAgendamento.DataHoraConsulta = #tbHoraMarcada.HoraConsulta)
               left outer join tbFeriado on(tbFeriado.DiaMes = #tbHoraMarcada.DiaMes)

     

     

    [ ]s,

     

    Gustavo

    segunda-feira, 11 de fevereiro de 2008 18:39
  • Rapaz,

     

    Que dificuldade hein.....Continua não mostrando o nome....

     

    Um detalhe: As consultas que estão agendas, essas sim está mostrando o nome do dentista e o que eu quero é extamente ao contrario mostrar os nomes dos que não estão marcados.

     

    Obrigado.

     

    segunda-feira, 11 de fevereiro de 2008 23:04
  • Olá Gilberto,

     

    Se o nome do dentista não está aparecendo só existe duas razões possíveis:

     

    - Nem todo agendamento tem um dentista (o que você já confirmou que não é verdade por conta da coluna NOT NULL)

    - Nem todo dentista está na tabela de funcionários

     

    As tabelas Agendamento e Funcionario tem relacionamentos ? Há uma FK em agendamento referenciando Funcionarios ?

     

    [ ]s,

     

    Gustavo

     

    terça-feira, 12 de fevereiro de 2008 11:55
  •  

    Gilberto / Gustavo,

     

    O que estou vendo e entendi é o seguinte, os dados que estão sendo selecionados até o momento (aquela tabela temporária) estão sendo usados para mostrar os horários em que não existe agendamento, certo?? Se não tem agendamento, óbviamente não tem "dentista" também.

     

    Pelo que entendi, você quer modificar a solução para poder mostrar os horários em que cada médico está livre, certo??? Então, se for isso, terá que alterar aquela consulta inicial, colocando também o ID dos dentistas que estão livres.

     

     

    Abraço

    terça-feira, 12 de fevereiro de 2008 12:19
  • Caro Alexandre,

     

    É exatamente isso que preciso mostrar os horários em que cada dentista está livre.....

     

    Ao Gustavo: Me expressei mal quando disse que dificuldade hein......quis me referir a minha dificuldade de expressar e fazer o script funcionar e não em relação a você.

     

    Obrigado.

    terça-feira, 12 de fevereiro de 2008 14:27
  •  

    Gilberto,

     

    Sua necessidade definitivamente não é algo comum!! hehe

     

    Bom, cheguei em casa e coloquei o tico e o teco pra funcionar, queimei alguns neurônios, até montei um exemplo para simular seu problema. Acabei deixando a estrutura da sua procedure exatamente como estava, apenas mudei o select. Veja o resultado:

     

    Code Snippet

    alter procedure pHoraMarcada

    (

    @IDataHora datetime,

    @FDataHora datetime

    )

    as

    declare @Dia integer, @Mes integer, @DiaMes varchar(5)

    create table #tbHoraMarcada -- tabela temporária

    (

    HoraConsulta dateTime,

    DiaMes varchar(5)

    )

    while(@IDataHora <= @FDataHora)

    begin

    -- o intervalo entre as consulta é de 30 minutos

    set @IDataHora = dateadd(n,30,@IDataHora)

    set @Dia = datepart(dd, @IDataHora)

    if(@Dia < 10)

    set @DiaMes = '0' + cast(@Dia as varchar) + '/'

    else

    set @DiaMes = cast(@Dia as varchar) + '/'

    set @Mes = datepart(mm, @IDataHora)

    if(@Mes < 10)

    set @DiaMes = @DiaMes + '0' + cast(@Mes as varchar)

    else

    set @DiaMes = cast(@DiaMes as varchar)

    insert into #tbHoraMarcada (HoraConsulta, DiaMes)

    values (@IDataHora, @DiaMes)

    end

    -- esse é o select

    select sub.HoraConsulta, sub.DiaMes, sub.Dentista, sub.DiaSemana

    from ( -- início da subquery que vai fazer a mágica

    select

    H.HoraConsulta, H.DiaMes, TF.Nome As Dentista, TF.idDentista,

    dbo.nDiaSemana(datepart(dw,H.HoraConsulta)) as DiaSemana

    from #tbHoraMarcada H

    cross join tbFuncionario TF

    left join tbFeriado F on F.DiaMes = H.DiaMes

    where

    datepart (dw,H.HoraConsulta) between 2 and 6

    and F.DiaMes is null

    ) sub -- fim da subquery, "sub" é o nome da dela

    left join tbAgendamento A

    on A.DataHoraConsulta = sub.HoraConsulta and A.IdDentista = sub.idDentista

    where A.idDentista is null

     

     

    Bom, espero que resolva seu problema.

     

     

    Grande abraço!!!

     

    quarta-feira, 13 de fevereiro de 2008 02:01
  • Caro Alexandre,

     

    Obrigado pelo empenho nessa ajuda. Vou estudar o que você fez par que eu possa compreender testei seu select só está dando um erro:

     

    Invalid column name 'idDentista'.

     

    Alterei  em vários lugares mais o probelam continua.

     

    Me responda uma ciosa o que CROSS join

     

    Obrigado.

    quarta-feira, 13 de fevereiro de 2008 02:31
  •  

    Acordado essa hora Gilberto?? hehe (brincadeirinha)...

     

    Sobre o erro, falha minha... Aqui está, corrigido:

     

    Code Snippet

    alter procedure pHoraMarcada

    (

    @IDataHora datetime,

    @FDataHora datetime

    )

    as

    declare @Dia integer, @Mes integer, @DiaMes varchar(5)

    create table #tbHoraMarcada -- tabela temporária

    (

    HoraConsulta dateTime,

    DiaMes varchar(5)

    )

    while(@IDataHora <= @FDataHora)

    begin

    -- o intervalo entre as consulta é de 30 minutos

    set @IDataHora = dateadd(n,30,@IDataHora)

    set @Dia = datepart(dd, @IDataHora)

    if(@Dia < 10)

    set @DiaMes = '0' + cast(@Dia as varchar) + '/'

    else

    set @DiaMes = cast(@Dia as varchar) + '/'

    set @Mes = datepart(mm, @IDataHora)

    if(@Mes < 10)

    set @DiaMes = @DiaMes + '0' + cast(@Mes as varchar)

    else

    set @DiaMes = cast(@DiaMes as varchar)

    insert into #tbHoraMarcada (HoraConsulta, DiaMes)

    values (@IDataHora, @DiaMes)

    end

    -- esse é o select

    select sub.HoraConsulta, sub.DiaMes, sub.Dentista, sub.DiaSemana

    from ( -- início da subquery que vai fazer a mágica

    select

    H.HoraConsulta, H.DiaMes, TF.Nome As Dentista, TF.idFuncionario,

    dbo.nDiaSemana(datepart(dw,H.HoraConsulta)) as DiaSemana

    from #tbHoraMarcada H

    cross join tbFuncionario TF

    left join tbFeriado F on F.DiaMes = H.DiaMes

    where

    datepart (dw,H.HoraConsulta) between 2 and 6

    and F.DiaMes is null

    ) sub -- fim da subquery, "sub" é o nome da dela

    left join tbAgendamento A

    on A.DataHoraConsulta = sub.HoraConsulta and A.IdDentista = sub.idFuncionario

    where A.idDentista is null

     

     

     

    Eu não me atentei do fato de ser idFuncionario na tabela tbFuncionario, agora deve funcionar.

     

     

    Sobre o CROSS JOIN, ele simplesmente faz um produto cartesiano entre as tabelas, exemplo:

    Tabela1

    ID1, Nome

    1, Alexandre

    2, Gilberto

    3, Fulado

     

    Tabela2

    ID2, Cor

    1, Azul

    2, Amarelo

     

    Se for feito um CROSS JOIN entre elas, desta forma:

    Code Snippet

    SELECT ID1, Nome, ID2, Cor

    FROM Tabela1

    CROSS JOIN Tabela2

     

     

    O resultado será:

    1, Alexandre, 1, Azul

    1, Alexandre, 2, Amarelo

    2, Gilberto, 1, Azul

    2, Gilberto, 2, Amarelo

    3, Fulano, 1, Azul

    3, Fulano, 2, Amarelo

     

    Resumindo, ele vai relacionar todos com todos.

     

    No seu caso, foi usado para gerar uma lista com todos funcionários e possíveis horários em que podem ter consulta marcada, depois fiz um LEFT JOIN com esse resultado mas retornei apenas aqueles q não se tem uma correspondência na tabela de Agendamentos.

     

     

    Espero ter explicado direitinho, hehe.

     

     

    Abraço!!!

    quarta-feira, 13 de fevereiro de 2008 02:46
  •  

    Gilberto,

     

    Chegou a testar a solução "corrigida" que montei??

     

     

    Abraço!!

    quarta-feira, 13 de fevereiro de 2008 19:00
  • Então Alexandre,

     

    Dei uma arrumada no script. Veja:

     

    CREATE procedure pHoraMarcada
    (
           @IDataHora datetime,
           @FDataHora datetime
    )
    as
    declare @Dia integer, @Mes integer, @DiaMes varchar(5)
    begin
        create table #tbHoraMarcada -- tabela temporária
        (
               HoraConsulta dateTime,
               DiaMes varchar(5)
        )
        while(@IDataHora <= @FDataHora)
        begin -- o intervalo entre as consulta é de 30 minutos
            set @IDataHora = dateadd(n,30,@IDataHora)
            set @Dia = datepart(dd, @IDataHora)
            if(@Dia < 10)
              set @DiaMes = '0' + cast(@Dia as varchar) + '/'
            else
              set @DiaMes = cast(@Dia as varchar) + '/'
            set @Mes = datepart(mm, @IDataHora)
            if(@Mes < 10)
              set @DiaMes = @DiaMes + '0' + cast(@Mes as varchar)
            else
            set @DiaMes = cast(@DiaMes as varchar)
            insert into #tbHoraMarcada
            (
                   HoraConsulta,
                   DiaMes
            )
            values
            (
                  @IDataHora,
                  @DiaMes
            )
        end
        -- esse é o select
        select sub.HoraConsulta, sub.DiaMes, sub.Dentista, sub.DiaSemana
               from  -- início da subquery que vai fazer a mágica
               (
                     select Hora.HoraConsulta, Hora.DiaMes, Func.Nome As Dentista,
                            Func.idFunc, Funcao.Funcao,
                            dbo.nDiaSemana(datepart(dw,Hora.HoraConsulta)) as DiaSemana
                     from #tbHoraMarcada Hora
                     cross join tbFuncionario Func
                     left join tbFeriado Feriado on Feriado.DiaMes = Hora.DiaMes
                     left join tbFuncao Funcao on Funcao.idFuncao = Func.idFuncao
               where
                    datepart(dw,Hora.HoraConsulta) between 2 and 6 -- 2 = Segunda, 6 = Sexta
               and
                    Feriado.DiaMes is null
               and
                    Funcao.Funcao = 'DENTISTA'
               ) sub -- fim da subquery, "sub" é o nome da dela
               left join tbAgendamento Agenda on Agenda.DataHoraConsulta = sub.HoraConsulta
               and
                    Agenda.IdDentista = sub.idFunc
        where Agenda.idDentista is null
    end

     

    Acrescentei a tabela Funcao por que eu precisava mostrar somente os funcionários cuja a função seja "DENTISTA" e funcionou beleza é exatamente isso que estava querendo. Fiz vários testes e respondeu corretamente à minha necessidade.

     

    Bom: Quero te agradecer muito pelo interesse que demonstrou por esse problema. Muito obrigado mesmo.

     

    Com  relação ao exemplo do cross join(com a seleção das cores), se eu usar o UNION ALL teria o mesmo efeito. 

     

    Um abraço.

     

     

     

    quarta-feira, 13 de fevereiro de 2008 23:18
  •  

    Gilberto,

     

    Que bom que funcionou.

     

    É tipo de desafio que eu gosto. Coisas que fazem a cabeça esquentar de tanto pensar.

     

     

    Sempre que tiver algum problema, pode retornar

     

     

    Grande abraço!!!

    quinta-feira, 14 de fevereiro de 2008 01:38