Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
immokk1
Starting Member
2 Posts |
 Posted - 2012-10-31 : 04:36:56
|
| Hi all, I'm relatively new to SQL and have a query to write which will use a table:'Admittable'and bring back all incidents where a patient has been readmitted with 28 days of discharge, over a two year period. I can get a query to do this within 28 days of their first discharge but what I need is the following; If all admissions for one patient looks like the below table: Patientid AdmitDate DischargeDate 1 11 01-03-2011 02-03-2011 2 11 05-03-2011 08-03-2011 3 11 10-03-2011 12-03-2012 4 11 15-04-2011 17-04-2011 5 11 18-04-2011 20-04-2011 6 11 29-05-2011 30-05-2011 7 11 15-06-2011 16-06-2011From the above table I would only wish to see 2, 3, 5 and 7. I don't want to see 1 because it is a first admission. I want to see any admission date within 28 days of the discharge date of 1 (2 and 3).4 is not within 28 days of 1 but is within my overall timeframe so this would effectively be a new admission so I would not want to see it but would then want to see anything with an admitdate within 28 days 4's discharege date. (so I would want 2,3,5 and 7 and treat 1,4 and 6 as start dates).Does this make sense? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-10-31 : 05:17:22
|
| [code]declare @patient TABLE (id int, Patientid int, AdmitDate date, DischargeDate date)INSERT INTO @patient SELECT 1, 11, '01-03-2011', '02-03-2011' UNION ALLSELECT 2, 11, '05-03-2011', '08-03-2011' UNION ALLSELECT 3, 11, '10-03-2011', '12-03-2011' UNION ALLSELECT 4, 11, '15-04-2011', '17-04-2011' UNION ALLSELECT 5, 11, '18-04-2011', '20-04-2011' UNION ALLSELECT 6, 11, '29-05-2011', '30-05-2011' UNION ALLSELECT 7, 11, '15-06-2011', '16-06-2011'SELECT p1.*, datediff(dd, p2.AdmitDate,p1.DischargeDate) 'NoOfDays'FROM @patient p1 JOIN @patient p2 ON p1.id = p2.id+1WHERE datediff(dd, p2.AdmitDate,p1.DischargeDate) <= 28[/code] |
 |
|
|
immokk1
Starting Member
2 Posts |
Posted - 2012-10-31 : 07:31:45
|
| Hi Bandi, Thank you for the reply. I htink my post was misleading. The table I showed was an example for one patient of what I might get. I have several thousand patients (with a patientid unique to them) and two years worth of data that the query needs to trawl through. So, for each patient I need; their first discharge date (between 01-04-2010 and 31-03-2012) and from that initial discharge date any following admitdate that falls within 28 days (so where the difference between the initial dischargedate and their subsequent admitdates is <29). The complication comes when:Once the query finds the first admitdate that does not fall within 28 days of the patients initial discharge date it then needs to view this new date as the start of another 28 days. and repeat until there are none left. So, each patient has a unique id and each row in the table will represent an admission. Each admission has an admit date and a discharge date. In the two year period 01-04-2010 to 31-03-2012 I need a list of these admissions that fall within 28 days of another discharge date (but not within 28 days of a discharge date that is considered a re-admission itself by falling within 28 days of the initial discharge date).I know this is confusing, and its why I am having so much trouble with it.I can do the first element by matching the table back on itself and finding the first ever discharge and then comparing that with any later admissions for that patient that occur within 28 days. I have a problem when it comes to making the next one outside of the 28 days a new initial date.I'm sorry! |
|
|
|
|
|
|
|
|
SQL - Re-admissions within 28 days