CODEWITHSUNDEEP
mysql
Rafael Melo Silva
Rafael Melo Silva

Reputation: 9

Count id for each day in a month

I have a database in mysql for a hospital where the columns are: id, entry_date, exit_date (the last two columns are the hospital patient entry and exit). I would like to count the number of patients on each day of a given month

The code to count the number of ids for a given day is relatively simple (as described), but the count for each day of an entire month i do not know how to do.

Day 2019-09-01: x patients
Day 2019-09-02: y patients
Day 2019-09-03: z patients
.
.
.
x + y + z + ... = total patients on each day for all days of september

SELECT Count(id) AS patientsday
FROM   saps
WHERE  entry_date <= '2019-05-02'
       AND ( exit_date > '2019-05-02'
              OR exit_date IS NULL )
       AND hospital = 'X'

Upvotes: 0

Views: 120

Answers (2)

dazed-and-confused
dazed-and-confused

Reputation: 1333

This will break it down for number of entries for all dates. You can modify the SELECT to add a specific month and/or year.

SELECT
    CONCAT(YEAR, '-', MONTH, '-', DAY) AS THE_DATE,
    ENTRIES
FROM (
    SELECT 
        DATE_FORMAT(entry_date, '%m') AS MONTH,
        DATE_FORMAT(entry_date, '%d') AS DAY,
        DATE_FORMAT(entry_date, '%Y') AS YEAR,
        COUNT(*) AS ENTRIES
    FROM 
        saps
    GROUP BY 
          MONTH,
          DAY,
          YEAR
) AS ENTRIES
ORDER BY
    THE_DATE DESC

Upvotes: 0

Mariane Reis
Mariane Reis

Reputation: 11

First, assuming every day there is at least one patient entering this hospital, I would write a temporary table containing all the possibles dates called all_dates.

Second, I would create a temporary table joining the table you have with all_dates. In this case, the idea is to duplicate the id. For each day the patient was inside the hospital you will have the id related to this day on your table. For example, before your table looked like this:

id  entry_date  exit_date
1   2019-01-01  2019-01-05
2   2019-01-03  2019-01-04
3   2019-01-10  2019-01-15

With the joined table, your table will look like this:

id  possible_dates
1   2019-01-01
1   2019-01-02
1   2019-01-03
1   2019-01-04
1   2019-01-05
2   2019-01-03
2   2019-01-04
3   2019-01-10
3   2019-01-11
3   2019-01-12
3   2019-01-13
3   2019-01-14
3   2019-01-15

Finally, all you have to do is count how many ids you have per day.

Here is the full query for this solution:

WITH all_dates AS (
  SELECT distinct entry_date as possible_dates
  FROM your_table_name
 ),

patients_per_day AS (
    SELECT id
         , possible_dates
    FROM all_dates ad
    LEFT JOIN your_table_name di
         ON ad.possible_dates BETWEEN di.entry_date AND di.exit_date 
)

SELECT possible_dates, COUNT(ID)
FROM patients_per_day
GROUP BY 1

Another possible solution, following almost the same strategy, changing only the conditons of the join is the query bellow:

WITH all_dates AS (
  SELECT distinct entry_date as possible_dates
  FROM your_table_name
 ),

date_intervals AS (
    SELECT id 
         , entry_date
         , exit_date
         , datediff(entry_date, exite_date) as date_diference
    FROM your_table_name
),

patients_per_day AS (
    SELECT id
         , possible_dates
    FROM all_dates ad
    LEFT JOIN your_table_name di
         ON datediff(ad.possible_dates,di.entry_date)<= di.date_diference
)

SELECT possible_dates, COUNT(ID)
FROM patients_per_day
GROUP BY 1

Upvotes: 1

Related Questions