I need to relate two tables in order to bring me a result within the conditions I want

Question

I’m using the SQLSERVER

Condition(What the result should return to me) Creation of the agenda. Necessary creation of the flow for creation of agenda and blocking to reserve the vacancies;

When cross-referencing the data from two tables I need to assign a spot to that patient who came in contact first. And this vacancy can no longer be attributed to any other patient.

To do this I thought about creating a precedent, but I can’t get the syntax right, help me please, or if there’s any other way to do that too

CREATE PROCEDURE OcuparVaga
AS
BEGIN
-- Criando tabela de retorno da proc
    SELECT CodPaciente, NomePaciente, NULL NomeEspecialidade, NULL NomeMedico,Telefone, 
    Celular, NULL DataVaga, NULL HoraVaga, 0 StatusVaga, NULL Obs
    INTO #Retorno
    FROM  Paciente WHERE StatusSolic = 0

-- Declarando variavel para ser utilizada no while
      DECLARE @i INT
        SET @i = 0

-- Tabela temporaria para controle do while
    SELECT * INTO #Solicitacoes FROM Paciente WHERE StatusSolic =0

-- Verificando se existe Pacientes com status igual a 0(disponivel)
    IF (SELECT COUNT(*) FROM #Solicitacoes) ==0
    BEGIN
        RETURN 'Não há Pacientes a serem atribuido a vaga'
    END
-- Iniciando o while
    WHILE @i < (SELECT COUNT(*) FROM #Solicitacoes)
    BEGIN
        DECLARE @CodPaciente INT --Variavel que representará a linha da tabela Paciente que estamos executando

----Preenchendo a váriavel com a menor data/hora (conforme definido a prioridade)
        SELECT @CodPaciente = CodPaciente FROM #Solicitacoes
        ORDER BY DataContato, HoraContato

--- Pegar a primeira linha com a menor data/hora dentre as vagas disponíveis para atribuir ao Paciente de acordo com a especialidade, regra de data e statusVaga

        SELECT TOP 1 a.* INTO #Disponivel FROM Horarios AS a
        INNER JOIN Paciente AS s ON a.CodEspecialidade = s.CodEspecialidade
        WHERE a.DataHorarios >= DATEADD(day, 3, s.DataContato)
        AND a.StatusVaga=0 AND s.CodPaciente = @CodPaciente

        DECLARE @CodHorarios INT = 0 --Será utilizada para o preenchimento da vaga
        SELECT @CodHorarios = CodHorarios FROM #Disponivel -- Setar na variável o id da vaga disponível que está na tabela temporária


--Se existir uma vaga disponível, o status da vaga e do Paciente serão alterados
        IF @CodHorarios >0
        BEGIN
            UPDATE Horarios SET StatusVaga = 1, CodPaciente = @CodPaciente
            WHERE CodHorarios = @CodHorarios

            UPDATE Paciente SET StatusSolic = 1
            WHERE CodPaciente = @CodPaciente

            UPDATE #Retorno SET NomeEspecialidade = a.NomeEspecialidade,
                        NomeMedico = a.NomeMedico,
                        DataVaga = a.DataHorarios, HoraVaga = a.HoraHorarios,
                        StatusVaga = 1, Obs = 'Vaga preenchida com sucesso!'
            FROM #Retorno r
            INNER JOIN Horarios AS a ON r.CodPaciente = a.CodPaciente
            WHERE a.CodHorarios = @CodHorarios
        END
        ELSE
        BEGIN
            UPDATE #Retorno SET Obs = 'Não foi encontrado um horário para a especialidade desejada.'
            WHERE CodPaciente = @CodPaciente
        END
        -- Deleta a linha atual para o while seguir para a próxima
        DELETE FROM #Solicitacoes WHERE CodPaciente = @CodPaciente
        SET @i= @i+1 --Incrementa +1 no indice do while
    END
SELECT * FROM #Retorno
END
GO

Answer 1

When cross-referencing the data from two tables I need to assign a spot to that patient who came in contact first. And this vacancy can no longer be attributed to any other patient.

The ideal is to build codes that treat the lines in blocks (data set) and not one line at a time (row by row). For small tables it makes no difference, but for large volumes the increase in processing time is huge.

Here is draft suggestion, which deals with the allocation of requests for consultations with the available vacancies following the rule "assign a position to that patient who came in contact first”:

-- código #1 v4
declare @PacOk table (CodPaciente int, CodHorarios int);

BEGIN TRANSACTION;

with 
grupoPaciente as (
SELECT *, Seq= row_number() over (partition by CodEspecialidade 
                                  order by DataContato, HoraContato)
  from Paciente
  where StatusSolic = 0
),
grupoVaga as (
SELECT *, Seq= row_number() over (partition by CodEspecialidade 
                                  order by DataHorarios, HoraHorarios)
  from Horarios
  where StatusVaga = 0
)
UPDATE gV
  set CodPaciente= gP.CodPaciente,
      StatusVaga= 1
  output inserted.CodPaciente, inserted.CodHorarios into @PacOk
  from grupoVaga as gV
       inner join grupoPaciente as gP on gP.CodEspecialidade = gV.CodEspecialidade
                                         and gP.Seq = gV.Seq;

UPDATE Paciente
  set StatusSolic= 1
  where CodPaciente in (SELECT CodPaciente from @PacOk);

COMMIT TRANSACTION;

-- relata vagas que foram alocadas nesta execução
SELECT H.CodPaciente, P.NomePaciente, P.Telefone, P.Celular, 
       H.CodEspecialidade, H.NomeEspecialidade, H.NomeMedico, 
       DataVaga= H.DataHorarios, HoraVaga= H.HoraHorarios, 
       'Consulta marcada' as Obs
  from Horarios as H
       inner join Paciente as P on P.CodPaciente = H.CodPaciente
  where H.CodHorarios in (SELECT CodHorarios from @PacOk);

-- relata solicitações de atendimento não marcadas
SELECT CodPaciente, NomePaciente, Telefone, Celular, CodEspecialidade,
       'Não há horário disponível para a especialidade desejada' as Obs
  from Paciente
  where StatusSolic = 0;

---

Image with test result:

---

Structure and data used for testing:

-- código #2
CREATE TABLE Horarios (
  CodHorarios int,
  CodEspecialidade varchar(20),
  NomeEspecialidade varchar(30),
  CodMedico int,
  NomeMedico varchar(30),
  DataHorarios date,
  HoraHorarios time(0),
  CodPaciente int default 0,
  StatusVaga tinyint default 0
);

CREATE TABLE Paciente (
  CodPaciente int,
  NomePaciente varchar(30),
  CodEspecialidade varchar(20),
  Telefone varchar(20),
  Celular varchar(20),
  DataContato date,
  HoraContato time(0),
  StatusSolic tinyint default 0
);

set dateformat dmy;

TRUNCATE TABLE Horarios;
INSERT into Horarios values
  (1, '004 3/02', 'ALERGIA - ASM', 1, 'Abílio de Barros', '9/5/2018', '7:00', 0, 0),
  (2, '004 3/04', 'CLINICO', 3, 'Roberto', '10/5/2018', '7:10', 0, 0),
  (3, '004 3/02', 'ALERGIA - ASM', 1, 'Abílio de Barros', '11/5/2018', '7:20', 0, 0),
  (4, '004 3/03', 'CARDIOLOGIA', 2, 'Fernanda', '9/5/2018', '7:00', 0, 0);

TRUNCATE TABLE Paciente;
INSERT into Paciente values 
  (1, 'José', '004 3/02', '(11)5789-8547', '(11)92485-6128', '6/5/2018', '7:00', 0),
  (2, 'Maria', '004 3/04', '(11)5789-7857', '(11)9455-6187', '9/5/2018', '7:05', 0),
  (3, 'Hugo', '004 3/03', '(11)5619-8548', '(11)97085-2630', '9/5/2018', '7:30', 0),
  (4, 'Natália', '004 3/02', '(11)5529-3849', '(11)9278-6241', '6/5/2018', '7:15', 0);

Answer 2

@Josédiz I’m almost there, that result brought me:

Patient Joseph assigned to all other specialties

And when I change the query that brings me the allocated vacancies. putting

SELECT H.Codpatient, P.Patient name, P.Telephone, P.Cellular, H.Codespeciality, H.Nomespecialty, H.Nomemedic, Date= H.Date, Time= H.Time, 'Appointment marked' as Obs from Horarios as H Patient as P on P.Codpatient = H.Codpatient Where H.Codespeciality = P.Codespeciality AND Codhorarios in (SELECT Codhorarios from @Pacok);

He brings me, only the specialty that Jose requested, but assigning to him the two available schedules, where should be only the first wave: