October 28, 2016 at 10:00 pm
Hello all,
I need some help from you great minds here. I would like some insight on how to calculate the percent of readmission from a discharge within the typical readmission parameter of 30 days
I have kind of put together a starting point script-wise, but I need to further develop the script to answer my inquiry.
select
ReportingYear
RegArea,
DateOfAdmission OrignalAdmission,
Dischargedate OrignalDischarge,
DateOfAdmission Readmission,
Dischargedate,
datediff(day, Dischargedate, DateOfAdmission )
from fRd 01
inner join ftA 02 on
01.Serial = 02.Serial and
02.Ind = 'Inpatient'
inner join ftA 03 on
01.TrigSerial = 03.Serial and
03.Ind = 'Inpatient'
Below is a sample of the underlying data. Please excuse me if I did not use the correct code, and for the headers not being completely aligned.
Reporting YearReg AreaOrignalAdmissionOrignalDischargeReadmissiondischargedatedays between discharge and readmissions
2013Hamb17/1/20137/8/20137/8/20137/17/20130
2014Hamb25/7/20145/17/20145/28/20146/14/201411
2015Hamb32/5/20152/10/20152/25/20153/3/201515
2011Hamb410/22/201110/25/201111/23/201112/1/201129
October 29, 2016 at 12:38 am
Use this as a starting point
WITH cteReAdmission AS (
SELECT ReportingYear, RegArea
, isReAdmission = CASE WHEN DATEDIFF(DAY,
LAG(DischargeDate, 1, DateOfAdmission) OVER(PARTITION BY patient
ORDER BY DateOfAdmission, DischargeDate),
DateOfAdmission
) <= 30 THEN 1 ELSE 0 END
FROM YourDataSourceHere
)
SELECT
Admissions = count(*)
, ReAdmissions = sum(isReAdmission)
, percReAdmission = 100 * sum(isReAdmission) /count(*)
FROM cteReAdmission
GROUP BY ReportingYear, RegArea
October 29, 2016 at 6:28 pm
Thanks for the response. I'm still a bit confused. I'm trying to employ your CTE example, but I'm getting constant errors, mainly in the case statement. I know this is an example you provided, however, I'm trying to model my logic somewhat like what you have.
October 30, 2016 at 1:07 am
Briceston (10/29/2016)
Thanks for the response. I'm still a bit confused. I'm trying to employ your CTE example, but I'm getting constant errors, mainly in the case statement. I know this is an example you provided, however, I'm trying to model my logic somewhat like what you have.
My apologies. That's the second time in 2 days that I have provided a SQL 2012 solution to a SQL 2008 problem.
Please take a look at this code
CREATE TABLE #Data (
ReportingYear int
, RegArea varchar(50)
, PatientID int
, AdmissionDate date
, DischargeDate date
);
INSERT INTO #Data ( ReportingYear, RegArea, PatientID, AdmissionDate, DischargeDate )
VALUES ( 2013, 'Hamb1', 1, '7/1/2013', '7/8/2013' )
, ( 2013, 'Hamb1', 1, '7/8/2013', '7/17/2013') -- 0
, ( 2014, 'Hamb2', 2, '5/7/2014', '5/17/2014' )
, ( 2014, 'Hamb2', 2, '5/28/2014', '6/14/2014' ) -- 11
, ( 2015, 'Hamb3', 3, '2/5/2015', '2/10/2015' )
, ( 2015, 'Hamb3', 3, '2/25/2015', '3/3/2015' ) -- 15
, ( 2011, 'Hamb4', 4, '10/22/2011', '10/25/2011' )
, ( 2011, 'Hamb4', 4, '11/23/2011', '12/1/2011' ) -- 29
WITH cteData AS (
SELECT ReportingYear, RegArea, PatientID, AdmissionDate, DischargeDate
, rn = ROW_NUMBER() OVER (PARTITION BY RegArea, PatientID ORDER BY AdmissionDate, DischargeDate)
FROM #Data
)
, cteReAdmission AS (
SELECT cur.ReportingYear, cur.RegArea, cur.PatientID, cur.AdmissionDate, cur.DischargeDate
, isReAdmission = CASE WHEN DATEDIFF(DAY, prev.DischargeDate, cur.AdmissionDate
) <= 30 THEN 1 ELSE 0 END
FROM cteData AS cur -- Current Record
LEFT JOIN cteData AS prev -- Previous Record
ON cur.RegArea = prev.RegArea
AND cur.PatientID = prev.PatientID
AND cur.rn = prev.rn +1
)
SELECT ReportingYear, RegArea
, Admissions = count(*)
, ReAdmissions = sum(isReAdmission)
, percReAdmission = 100 * sum(isReAdmission) /count(*)
FROM cteReAdmission
GROUP BY ReportingYear, RegArea
October 31, 2016 at 2:45 pm
Below is a sample of the underlying data. Please excuse me if I did not use the correct code, and for the headers not being completely aligned.
Reporting YearReg AreaOrignalAdmissionOrignalDischargeReadmissiondischargedatedays between discharge and readmissions
2013Hamb17/1/20137/8/20137/8/20137/17/20130
2014Hamb25/7/20145/17/20145/28/20146/14/201411
2015Hamb32/5/20152/10/20152/25/20153/3/201515
2011Hamb410/22/201110/25/201111/23/201112/1/201129
I am a bit confused on if the table you list above is the way you have your data or what you want the results of a query to be
----------------------------------------------------
November 1, 2016 at 8:50 pm
Thank you for your insight. Your example was not exactly what i needed to answer my question, mainly due to a new Id is issued for a readmission. I'm sure your logic will be helpful in the future. Thanks again.
November 2, 2016 at 11:26 am
Briceston (11/1/2016)
Thank you for your insight. Your example was not exactly what i needed to answer my question, mainly due to a new Id is issued for a readmission. I'm sure your logic will be helpful in the future. Thanks again.
Without sample data, proper DDL statements and expected output... I would not expect anyone to get it right the first time.
For future reference, you may want to read the link that I have at the bottom of my post here on a method of presenting your situation that gives readers a clear perspective a user's issue.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply