Question 192518 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL query to pull difference in data from previous day fetching incorrect data

There is a hospital database that has the following schema for admissions table:

patient_id  INT
admission_date  DATE
discharge_date  DATE
diagnosis   TEXT
room_id         INT
doctor_id   INT
insurance_id    INT

here’s what I am trying to query:

  • fetch the total amount of admissions for each day.
  • fetch the amount of admissions changed from the previous date.

I tried the following query:

SELECT
 admission_date,
 count(admission_date) as adm_day,
 count(admission_date) - count(admission_date - 1) AS admn_count_change // this count is not correct
FROM admissions
 group by admission_date

Query Result:
Data is showing correctly for the first two columns, the last column is showing incorrect data i.e a value of 0 in all rows and it is completely going over my head. I don’t use SQL much, please guide me.

Tags:

2

Answers


  1. 0

    You can use a combination of

    • GROUP BY to get the count per day
    • LAG windowing function to get the value for the previous row

    Code (Oracle syntax):

    with tmp_dat(pk, admission_date) as (
  select 1, date '2018-06-06' from dual union all
  select 2, date '2018-06-06' from dual union all
  select 3, date '2018-06-07' from dual union all
  select 4, date '2018-06-08' from dual
),
tmp_aggr as (
  SELECT
    admission_date,
    count(admission_date) as adm_day
  from tmp_dat
  group by admission_date
)
select a.*, 
       lag(adm_day) over (partition by 1 order by admission_date) as prev_adm_day,
       adm_day - lag(adm_day) over (partition by 1 order by admission_date) as adm_day_change
 from tmp_aggr a
 order by admission_date;
    Login or Signup to reply.
  2. 0

    Personally I would prefer to handle the calculation on the application side (or wherever the result of the query is used) as I’m no fan of queries which reference another row of itself.

    But a possible solution could be the use of a subselect where the date is used for the join.

    As no DBMS was tagged I’ve used PostgreSQL 15.
    Expect for the date calcuation for the join it should be useable with most DBMS.

    SELECT 
   admissions.admission_date, 
   count(admissions.admission_date), 
   count(admissions.admission_date)-calc.prevday as "admission_count_change" FROM admissions
LEFT JOIN (
    SELECT 
       admission_date, 
       count(admission_date) as "prevday" 
    FROM admissions
    GROUP BY admission_date
) as calc ON admissions.admission_date = ( calc.admission_date - interval '1 day')
GROUP BY admissions.admission_date, calc.prevday;

    The left join is necessary, so that the latest date will still appear (with admission_count_change null).

    I expect this query to scale not so nice with a large table.

    db<>fiddle to try it out yourself

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search