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

1

I'm using SQLSERVER

Condition (What the result should return me) · Creation of the agenda. It is necessary to create the flow for creation of agenda and blocking to reserve the vacancies;

When crossing data from two tables I need to allocate a spot for that patient who first came in contact. And that vacancy can no longer be attributed to any other patient.

To do this I thought about creating a procedure, but I can not seem to get the syntax correct, please help me, or if you have any other way to do that,

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
    
asked by anonymous 13.05.2018 / 02:28

2 answers

1
  

When crossing data from two tables I need to assign a spot to that patient who first contacted. And this vacancy can no longer be attributed to any other patient.

The ideal is to construct 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 a suggestion sketch, which deals with the allocation of query requests with available vacancies following the rule " assign a vacancy for that patient who first contacted":

-- 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);
    
13.05.2018 / 03:26
0

@ JoseDiz I'm almost there, this result brought me:

PatientJosephattributedtoallotherspecialties

AndwhenIchangethequerythatbringsmetheallocatedvacancies.placing

SELECTH.CodPatient,P.PatientName,P.Telephone,P.Cell,       H.CodSpeciality,H.SpecialName,H.MedicalName,       DataVaga=H.DataTime,HourTime=H.TimeTime,       'Checkmarked'asObs  fromSchedulestoH       innerjoinPatientasP.onPatient=H.CodPatient  whereH.CodSpeciality=P.CodSpecialityANDCodelogsin(SELECTCodelogsfrom@PacOk);

Hebringsme,onlythespecialtythatJoserequested,butassigninghimthetwoschedulesavailable,whereitshouldbeonlythefirstwave:

    
13.05.2018 / 21:02