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)
No related posts.
