How to Assign Vacancies from one table to Patients from another table

0

Galera for a project of dental consultations I need to relate two tables of patients and vacancies in order to assign the first patient who came in contact requesting a specific specialty (orthodontics, whitening, etc.) the first available wave available for that specialty .

The rules for finding this vacancy are:

  • You can not assign a slot very close to the schedule that you called
  • Each specialty has an expiration date (the last day of the month). If the patient contacts 3 days before the expiration date, the patient can only receive a vacancy on a date of the following month, if it is available.

The tables are:

create database ODONTO

create table Paciente(
 IDPac INT PRIMARY KEY IDENTITY,
 IDEspecialidade varchar(20),
 DTPac date,
 HRPac time(0),
 StatusSolic bit DEFAULT 0,
);

create table Vagas(
 IDVaga INT PRIMARY KEY IDENTITY,
 IDEspec varchar(20) NOT NULL,
 NomeEspec varchar(30) NOT NULL,
 DTVaga date NOT NULL,
 HRVaga time(0) NOT NULL,
 IDSolicitante int default 0,
 StatusVaga bit default 0
);

I found in an answer a question made here a code that only helps until you assign the vacancy, does anyone have any idea of what I can do to get the result?

i-- código #1 v4
DECLARE @PacOk TABLE (IDPac int, IDVaga int);

BEGIN TRANSACTION;

    WITH 
    grupoPaciente AS (
    SELECT *, Seq= row_number() OVER (partition BY IDEspec 
                                      order BY DTPac, HRPac)
    FROM Paciente
    WHERE StatusSolic = 0
    ),
    grupoVaga AS (
    SELECT *, Seq= row_number() over (partition BY IDEspec 
                                      order BY DTVaga, HRVaga)
    FROM Vaga
    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 CodPac IN (SELECT IDPac FROM @PacOk);

COMMIT TRANSACTION;

-- relata vagas que foram alocadas nesta execução
SELECT V.IDPac, P.NomePac,P.DTPac
       V.IDEspec, V.NomeEspec, 
       DataVaga= V.DTVaga, HoraVaga= V.HRVaga, 
       'Consulta marcada' AS Obs
FROM Vagas AS V
    INNER JOIN Paciente AS P ON P.CodPaciente = H.CodPaciente
WHERE H.IDVaga IN (SELECT IDVaga FROM @PacOk);

-- relata solicitações de atendimento não marcadas
SELECT IDPac, NomePac, IDEspec,
       'Não há horário disponível para a especialidade desejada' AS Obs
FROM Paciente
WHERE StatusSolic = 0;
    
asked by anonymous 21.05.2018 / 21:33

0 answers