SQL Server How to calculate readmission rates from inpatient admission and discharge dates?

wtzytmuj 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(82)

I'm trying to display in a Tableau Dashboard readmission rates per month in general and also by discharging service line. I have a table of MRN (ID, unique per patient), FIN (unique per encounter/stay but some patients will have multiple encounters), admission date, admitting service line (NICU, CCU, etc.), discharge date, discharging service line. The admitting and discharge service lines are most likely not going to be the same.

A readmission is defined as an admission after being discharged within 30 days.

Readmission rate is = # readmissions / # discharges.
| MRN | FIN | Admission Date | Admitting Service | Discharge Date | Discharging Service |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 707893 | 8072751999 | 2018-07-19 | NICU | 2018-07-30 | PCRS |
| 707893 | 8082751951 | 2018-08-15 | CCU | 2018-08-23 | CCU |
| 707812 | 8092884664 | 2018-12-02 | CCU | 2019-01-15 | PCRS |

I tried some code from another question just to get the normal readmission rate (not including service lines) but could not get it work. I want a table like the below as my end result to start, with eventually adding the service lines in there (discharging service line)

Month/Year	Total Number of Readmissions	Total Number of Discharges	Readmission Rate
Jan 2019	3	15	.20
Feb 2019	2	20	.10
March 2019	5	18	.27

Code I tried below (reg dt tm = admission date) with the following error:

(Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.)

WITH mnths AS (
  SELECT cast(1 AS int) m 
  UNION ALL SELECT m+1 FROM mnths WHERE m<48 
), admdis AS (
  SELECT *, 12*(YEAR(reg_dt_tm)-2018)+MONTH(reg_dt_tm)a, 12*(YEAR(disch_dt_tm)-2018)+MONTH(disch_dt_tm) d,
        (SELECT 1 FROM inpatient_pop WHERE med_rec_no=t.med_rec_no AND disch_dt_tm>DATEADD(day, -30, t.reg_dt_tm) AND FIN<t.FIN) readm
  FROM inpatient_pop t
)
SELECT (m-1)/12+2018 yr, 1+(m-1)%12 mn, COUNT(med_rec_no) totl ,COUNT(readm) readm, 
       CASE WHEN COUNT(med_rec_no)>0 THEN (0.+COUNT(readm))/ COUNT(med_rec_no) ELSE 0 END rate
FROM mnths
LEFT JOIN admdis ON a=m
GROUP BY m

sql-server

来源：https://stackoverflow.com/questions/76583829/how-to-calculate-readmission-rates-from-inpatient-admission-and-discharge-dates

1条答案

按热度按时间

6jjcrrmo1#

It's really helpful when you ask questions like this to include easily reproducible DDL/DML. I understand medical data is pretty sensitive, but there's nothing stopping you making some up. It might not be super realistic, but it gives us something to work with.

DECLARE @Admits TABLE (MRN BIGINT, FIN BIGINT, AdmitDate DATE, AdmitService NVARCHAR(5), DischargeDate DATE, DischargeService NVARCHAR(5));
INSERT INTO @Admits (MRN, FIN, AdmitDate, AdmitService, DischargeDate, DischargeService) VALUES
(1,  1,  '2023-04-01', 'CCU',  '2023-04-05', 'CCU'),
(4,  2,  '2023-04-01', 'ICU',  '2023-04-03', 'ICU'),
(2,  3,  '2023-04-02', 'NICU', '2023-04-05', 'NICU'),
(4,  4,  '2023-04-06', 'ICU',  '2023-04-07', 'ICU'),
(3,  5,  '2023-04-06', 'SCU',  '2023-04-05', 'SCU'),
(1,  6,  '2023-04-09', 'CCU',  '2023-04-12', 'CCU'),
(4,  7,  '2023-04-10', 'ICU',  '2023-04-15', 'ICU'),
(4,  8,  '2023-04-18', 'ICU',  '2023-05-10', 'ICU'),
(1,  9,  '2023-04-23', 'CCU',  '2023-05-26', 'CCU'),
(5,  10, '2023-04-23', 'CCU',  NULL,         'CCU'),
     
(6,  11, '2023-05-01', 'CCU',  '2023-05-05', 'CCU'),
(7,  12, '2023-05-01', 'ICU',  '2023-05-03', 'ICU'),
(8,  13, '2023-05-02', 'NICU', '2023-05-05', 'NICU'),
(9,  14, '2023-05-06', 'ICU',  '2023-05-07', 'ICU'),
(6,  15, '2023-05-06', 'SCU',  '2023-05-05', 'SCU'),
(6,  16, '2023-05-09', 'CCU',  '2023-05-12', 'CCU'),
(10, 17, '2023-05-10', 'ICU',  '2023-05-15', 'ICU'),
(11, 18, '2023-05-18', 'ICU',  '2023-05-30', 'ICU'),
(12, 19, '2023-05-23', 'CCU',  '2023-05-30', 'CCU'),
(4,  20, '2023-05-23', 'CCU',  NULL,         'CCU'),

(1,  21, '2023-06-01', 'CCU',  '2023-06-05', 'CCU'),
(12, 22, '2023-06-01', 'ICU',  '2023-06-03', 'ICU'),
(13, 23, '2023-06-02', 'NICU', '2023-06-05', 'NICU'),
(1 , 24, '2023-06-06', 'ICU',  '2023-06-14', 'ICU'),
(14, 25, '2023-06-06', 'SCU',  '2023-06-05', 'SCU'),
(11, 26, '2023-06-09', 'CCU',  '2023-06-12', 'CCU'),
(15, 27, '2023-06-10', 'ICU',  '2023-06-15', 'ICU'),
(13, 28, '2023-06-18', 'ICU',  NULL,         'ICU'),
(1 , 29, '2023-06-23', 'CCU',  NULL,         'CCU'),
(16, 20, '2023-06-23', 'CCU',  NULL,         'CCU');

Exactly how you do it is going to come down to what a readmission is qualified as. If you have a somewhat simplified rule of the same MRN is next admitted in a 10 day window, for example, you could try something like this:

SELECT DATEPART(YEAR,AdmitDate) AS Year, DATENAME(MONTH,AdmitDate) AS MonthName, DATEPART(MONTH,AdmitDate) AS Month, 
COUNT(*) AS Admissions, COUNT(Readmission) AS Readmissions, CAST(COUNT(Readmission) / (COUNT(*) +.0) AS DECIMAL(4,3)) AS ReadmissionRate
  FROM (
        SELECT MRN, FIN, 
               CASE WHEN LAG(DischargeDate,1) OVER (PARTITION BY MRN ORDER BY FIN) >= DATEADD(DAY,-10,AdmitDate) THEN 1 END AS Readmission, 
               AdmitDate, AdmitService, DischargeDate, DischargeService
         FROM @Admits 
       ) a
 GROUP BY DATEPART(YEAR,AdmitDate), DATENAME(MONTH,AdmitDate), DATEPART(MONTH,AdmitDate)
 ORDER BY Year, Month

Year	MonthName	Month	Admissions	Readmissions	ReadmissionRate
2023	April	4	10	4	0.400
2023	May	5	10	2	0.200
2023	June	6	10	5	0.500

Here we're using a LAG windowed function to literally look back at the last discharge date, and returning a 1 if it fits the criteria. You should be able to make this fit what ever your actual rules are by adjusting the CASE expression.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server How to calculate readmission rates from inpatient admission and discharge dates?

1条答案

相关问题

热门标签

最新问答