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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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) |