Readmission to hospital within 28 days of discharge

This is very common question when we deal with patients in NHS data. Here is how the task is defined – Readmission rates and HES. In short we need to know how many times(or when) a patient was readmitted within 28 days of his last hospital discharge. Here is how I do that in SQL with self join.

The dates are being transferred from “DDMMYYYY” to SQL server datetime “YYYY-mm-dd”.

You can always ad disease code or any other conditions to the query. Hope it helps!

The columns are:
[Column 8] – Patient identifier
[Column 11] – Admission date
[Column 15] – Method of admission
[Column 19] – Discharge date
[Column 28] – Spell end – Yes/No
[Column 123] – PCT code
[Column 187] – Unique record ID

SELECT a.[Column 8] as 'PatientID', a.[Column 15] as 'Method of Admission',
convert(datetime, STUFF(STUFF(a.[Column 11],3,0,'-'),6,0,'-'), 105) as 'Admission Date',
convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105) as 'Dischrage Date',
MIN(convert(datetime, STUFF(STUFF(b.[Column 11],3,0,'-'),6,0,'-'), 105)) AS 'Readmission Date',
left(a.[Column 123],3) as 'PCT',
DateDiff(dd, convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105), MIN(convert(datetime, STUFF(STUFF(b.[Column 11],3,0,'-'),6,0,'-'), 105))) AS 'How many days'
FROM Readmissions_data a
INNER JOIN Readmissions_data b ON a.[Column 8] = b.[Column 8]
	AND convert(datetime, STUFF(STUFF(b.[Column 11],3,0,'-'),6,0,'-'), 105) BETWEEN convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105) AND DATEADD(dd, 28, convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105))
WHERE a.[Column 28]='Y' AND a.[Column 19]<>'' AND (right(a.[Column 19],2)='09' OR right(a.[Column 19],2)='10') AND a.[Column 187]<>b.[Column 187]
GROUP BY a.[Column 8],a.[Column 15],left(a.[Column 123],3),convert(datetime, STUFF(STUFF(a.[Column 11],3,0,'-'),6,0,'-'), 105),convert(datetime, STUFF(STUFF(a.[Column 19],3,0,'-'),6,0,'-'), 105)
ORDER BY convert(datetime, STUFF(STUFF(a.[Column 11],3,0,'-'),6,0,'-'), 105)

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Read previous post:
Clean whole database from unwanted string

Often we end up having an unwanted string injected into our database. It might be a virus or any text...

Close