SQL request output the largest number of the previous month

62
March 15, 2019, at 8:20 PM

My schema:

I have:

doctors
id name             profession 
 1 James Harden     dental 
 2 James Jones      therapist 
 3 LeBron James     cardiologist 
 4 Kobe Braynt      surgeon 
 5 Sabrina Williams nurse 
 6 Tyler Okonma     speech therapist 
 7 John Snow        pediatrician
patients 
id name         diagnostic 
 1 Mo Bamba     tooth pulling out
 2 Kaney West   astma 
 3 Post Malone  heart attack 
 4 Denzel Curry headache 
 5 Nicola Jokic stomac-ache 
 6 Dwayne Wade  AIDS 
 7 Boo Aby      headahce
visits
doctorId  patientId  visitDate
1           1         2019-03-09
2           4         2019-03-01
2           5         2019-02-26
2           6         2019-02-05
3           3         2019-02-13
4           2         2019-03-07
7           1         2019-02-14
7           7         2019-02-15

I need to print the doctors serving the largest number of patients for the previous month.

My request is:

select d.name, g.counter
from doctors d inner join (
  select doctorid, count(distinct patientid) counter
  from visits
  where 
    year(visitdate) = year(current_date - interval 1 month)   -- return cur. year
    and 
    month(visitdate) = month(current_date - interval 1 month) -- return prev. month
  group by doctorid
  order by counter
) g on g.doctorid = d.id
order by g.counter desc, d.name 

results from my request:

   name        counter
   James Jones    2
   John Snow      2
   LeBron James   1 <- wrong

I need to output only like this:

       name        counter
       James Jones    2
       John Snow      2
Answer 1

The following gets the visits per doctor for the previous month:

select doctorid, count(distinct patientid) as counter
from visits v
where visitdate >= (curdate() - interval day(visitdate) - 1 day) - interval 1 month and
      visitdate < (curdate() - interval day(visitdate) - 1 day
group by doctorid;

Next, you need the top values. In MySQL 8+, you would use window functions. This is harder in older versions. One method is a having clause:

select doctorid, count(distinct patientid) as counter
from visits v
where visitdate >= (curdate() - interval day(visitdate) - 1 day) - interval 1 month and
      visitdate < curdate() - interval day(visitdate) - 1 day
group by doctorid
having count(distinct patientid) = (select count(distinct v2.patientid) as cnt
                                    from visits v2
                                    where v2.visitdate >= (curdate() - interval day(v2.visitdate) - 1 day) - interval 1 month and
                                          v2.visitdate < curdate() - interval day(v2.visitdate) - 1 day)
                                    group by v2.doctorid
                                    order by cnt desc
                                    limit 1
                                   );

Getting the name and other information is just a question of joining in other tables, which you seem to know how to do.

READ ALSO
Openproject installing to PostGre instead of MySQL

Openproject installing to PostGre instead of MySQL

Installing Openproject to a rented Ubuntu 1804 server

42
How to set mysql user variable at every new connection in Laravel 5.5?

How to set mysql user variable at every new connection in Laravel 5.5?

Background: We rely heavily on views in our app and one of the ways in which we are looking to optimize them is by parameterizationWe've got that aspect all figured out, but in order to make it work, I must be able to set a user parameter for the customer...

48
Mysql adding index for column type text does not improve performance while using select query

Mysql adding index for column type text does not improve performance while using select query

I am using InnoDBFrom this question, I found out that I have to specify the length if I want to add index to columns which type is TEXT

52
How to find cpanel credential? [on hold]

How to find cpanel credential? [on hold]

I am facing a mail server Debian 7Few years ago, roundcube 0

25