How to calculate monthly 30-days readmission rate from historical inpatient data in SQL?

Question

I want to calculate monthly readmission rate from inpatient data of 5 years (60) months. Some of the inpatients were readmitted more than 10 times in the time duration, while others were just once.

The data table looks like the following. As an example, inpatient with ID no. 201 was admitted only once in five years but ID no. 212 was 4 times and among them, two were readmissions within 3 days.

How can I calculate monthly 30-days readmission rates in the hospital with such data in SQL? 30-days readmission is when the inpatient is readmitted within 30 days of the previous discharge Thank you!

  ID   | SN    | Admdate     | Disdate       |
+------+-------+-------------+---------------+
|**212 | 1     | 2014-01-01  | 2014-01-12**  |
|201   | 2     | 2014-01-01  | 2014-01-10    |
|**212 | 598   | 2014-01-28  | 2014-02-12**  |
|**212 | 10000 | 2016-12-23  | 2016-12-29 ** |
| **212| 15112 | 2017-06-10  | 2017-06-21 ** |
|*401  | 20101 | 2018-01-01 | 2018-01-11     |*
|*401  | 21101 | 2018-02-01 | 2018-02-13     |*
|401   | 22101 | 2018-10-01  | 2018-10-11    |

Answer 1

Looks, like I am a bit late, but nonetheless - here is my suggestion:

WITH mnths AS (
  select cast(1 as int) m 
  UNION ALL select m+1 FROM mnths WHERE m<60 
), admdis AS (
  SELECT *, 12*(year(admdate)-2014)+month(admdate)a, 12*(year(disdate)-2014)+month(disdate) d 
  FROM tbl t
  WHERE exists (SELECT 1 FROM tbl WHERE id=t.id AND disdate>DATEADD(day, -30, t.admdate) and sn<t.sn) 
)
SELECT (m-1)/12+2014 yr, 1+(m-1)%12 mn,m, count(id) readm 
FROM mnths LEFT JOIN admdis ON a=m
GROUP BY m 

I set up a number table mnths to generate a regular grid of months for which I am then counting the readmissions. The CTE admdis only lists those patient admissions that happened less than 30 days after a previous discharge.

A demo can be found here: https://rextester.com/TLM57882

Edit:

When looking at forpas's solution I realised that I did not calculate the actual readmission rate. Here is a modified version that does exactly that:

WITH mnths AS (
  select cast(1 as int) m 
  UNION ALL select m+1 FROM mnths WHERE m<60 
), admdis AS (
  SELECT *, 12*(year(admdate)-2014)+month(admdate)a, 12*(year(disdate)-2014)+month(disdate) d,
        (SELECT 1 FROM tbl WHERE id=t.id AND disdate>DATEADD(day, -30, t.admdate) and sn<t.sn) readm
  FROM tbl t
)

SELECT (m-1)/12+2014 yr, 1+(m-1)%12 mn, count(id) totl ,count(readm) readm, 
       case when count(id)>0 THEN (0.+count(readm))/ count(id) ELSE 0 END rate
FROM mnths LEFT JOIN admdis ON a=m
GROUP BY m 

When calculating the rate I had to make sure that I did not "divide by zero", so , in those months where no admissions happened the rate is assumed to be zero (but not actually calculated).

demo: https://rextester.com/NFCXQ24711

And here is the generated result:

yr    mn totl readm rate
2014    1   3   1   0.333333333333
2014    2   0   0   0.000000000000
2014    3   0   0   0.000000000000
2014    4   0   0   0.000000000000
2014    5   0   0   0.000000000000
2014    6   0   0   0.000000000000
2014    7   0   0   0.000000000000
2014    8   0   0   0.000000000000
2014    9   0   0   0.000000000000
2014    10  0   0   0.000000000000
2014    11  0   0   0.000000000000
2014    12  0   0   0.000000000000
2015    1   0   0   0.000000000000
2015    2   0   0   0.000000000000
2015    3   0   0   0.000000000000
2015    4   0   0   0.000000000000
2015    5   0   0   0.000000000000
2015    6   0   0   0.000000000000
2015    7   0   0   0.000000000000
2015    8   0   0   0.000000000000
2015    9   0   0   0.000000000000
2015    10  0   0   0.000000000000
2015    11  0   0   0.000000000000
2015    12  0   0   0.000000000000
2016    1   0   0   0.000000000000
2016    2   0   0   0.000000000000
2016    3   0   0   0.000000000000
2016    4   0   0   0.000000000000
2016    5   0   0   0.000000000000
2016    6   0   0   0.000000000000
2016    7   0   0   0.000000000000
2016    8   0   0   0.000000000000
2016    9   0   0   0.000000000000
2016    10  0   0   0.000000000000
2016    11  0   0   0.000000000000
2016    12  1   0   0.000000000000
2017    1   0   0   0.000000000000
2017    2   0   0   0.000000000000
2017    3   0   0   0.000000000000
2017    4   0   0   0.000000000000
2017    5   0   0   0.000000000000
2017    6   1   0   0.000000000000
2017    7   0   0   0.000000000000
2017    8   0   0   0.000000000000
2017    9   0   0   0.000000000000
2017    10  0   0   0.000000000000
2017    11  0   0   0.000000000000
2017    12  0   0   0.000000000000
2018    1   1   0   0.000000000000
2018    2   1   1   1.000000000000
2018    3   0   0   0.000000000000
2018    4   0   0   0.000000000000
2018    5   0   0   0.000000000000
2018    6   0   0   0.000000000000
2018    7   0   0   0.000000000000
2018    8   0   0   0.000000000000
2018    9   0   0   0.000000000000
2018    10  1   0   0.000000000000
2018    11  0   0   0.000000000000
2018    12  0   0   0.000000000000

The column totl contains the total admissions in that period, readm the number of readmissions and rate is the ratio of readm/totl (or 0 in those cases where totl is 0).

Answer 2

With this:

with cte as (
  select 
    t.admdate,
    case when exists (
      select 1 from tablename
      where id = t.id and sn <> t.sn and datediff(day, disdate, t.admdate) <= 30
    ) then 1 else 0 end readmission
  from tablename t
  where datediff(month, t.admdate, getdate()) <= 60
)
select 
  year(admdate) year, 
  month(admdate) month,
  100.0 * sum(readmission) / count(*) readmissionrate
from cte
group by year(admdate), month(admdate) 
order by year(admdate), month(admdate)

The CTE returns a column readmission having the value 1 if that row of the table is a readmission or else 0.
Then by using group by year(admdate), month(admdate), you get the total number of readmissions with sum(readmission) and divide by the total number of rows.
See the demo.