SQL - Search for the names of patients who had more appointments in January 2016

Question

Hello, I would like to know how to do this consultation: "Find the name of the patients who had more appointments in January 2016".

 select * from PACIENTES  
 select * from CONSULTAS

These are the pictures from my table

Answer 1

The list of tables does not identify which table is each, but I assume that the first table is the table of consultations (because it contains the date and value of the consultation) and the second table is the table of patient information.

Here is a possible solution, which employs variables to store the consultation period and also the quantities of patients with more appointments to list.

-- código #1
-- quantidade de pacientes a listar
declare @QtdPL int;
set @QtdPL= 10; 

-- período a emitir
declare @DataInicio date, @DataFim date;
set @DataInicio= convert(date, '1/1/2016', 103);
set @DataFim= convert(date, '31/1/2016', 103);

--
with MaisConsultas as (
SELECT top (@QtdPL) RGPACIENTE, count(*) as QtdC
  from CONSULTAS
  where cast(DATA_HORA as date) between @DataInicio and @DataFim
  group by RGPACIENTE
  order by count(*) desc
)
SELECT A.RGPACIENTE, B.NMPACIENTE
  from MaisConsultas as A
       left join PACIENTES as B on B.RGPACIENTE = A.RGPACIENTE
  order by A.QtdC desc;

The above code has not been tested; report any questions.