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

The table listing does not identify what each table is, but I assume the first table is the one of queries (since it contains the date and the value of the query) and the second table is the one of information of the patients.

This is a possible solution, which uses variables to store the query period and also the number of patients with more queries 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; please let me know.