i want to display all the admissions of patients that happend within 30 days
Here is how i have the data in the admission table:
patient_id admid adm_date dis_date
215 12 2011-05-17 14:43:00.000 2011-05-19 13:50:00.000
421 13 2009-05-12 05:51:00.000 2009-05-13 18:35:00.000
417 15 2014-02-12 16:44:00.000 2014-02-17 14:35:00.000
417 17 2015-04-04 19:54:00.000 2015-04-05 15:00:00.000
865 18 2009-09-28 05:40:00.000 2009-09-29 10:00:00.000
992 19 2010-05-20 05:06:00.000 2010-05-25 17:51:00.000
421 20 2009-05-29 07:15:00.000 2009-05-31 13:45:00.000
421 17 2009-06-18 04:52:00.000 2009-06-19 10:55:00.000
421 18 2009-06-30 13:39:00.000 2009-07-23 17:57:00.000
326 21 2010-07-01 17:41:00.000 2010-07-01 17:41:00.000
326 14 2010-06-07 11:35:00.000 2010-06-08 15:58:00.000
215 16 2011-06-02 14:40:00.000 2011-06-03 13:25:00.000
i tried this query:
With adm as
(select patient_id, admid, adm_date, dis_date,row_number() over(partition by patient_id order by adm_date) as r
from admissions)
select a1.patient_id, a1.admid, a2.admid, a1.adm_date, a1.dis_date
from adm a1 inner join adm a2 on a1.patient_id=a2.patient_id
where a1.admid <> a2.admid and a1.r+1=a2.r
and a2.adm_date between a1.dis_date and dateadd(d,30, a1.dis_date)
order by a1.patient_id
I am getting the results as:
patient_id admid adm_date dis_date
215 12 2011-05-17 14:43:00.000 2011-05-19 13:50:00.000
421 13 2009-05-12 05:51:00.000 2009-05-13 18:35:00.000
421 20 2009-05-29 07:15:00.000 2009-05-31 13:45:00.000
421 17 2009-06-18 04:52:00.000 2009-06-19 10:55:00.000
326 14 2010-06-07 11:35:00.000 2010-06-08 15:58:00.000
but this is not the result i want because for patient 215 there are two admissions whose interval is less than 30 and i wanted to display both the admissions for this patient, but my result has only one admission.
i want the results to be displayed as below and not as the above:
patient_id admid adm_date dis_date
215 12 2011-05-17 14:43:00.000 2011-05-19 13:50:00.000
215 16 2011-06-02 14:40:00.000 2011-06-03 13:25:00.000
421 13 2009-05-12 05:51:00.000 2009-05-13 18:35:00.000
421 20 2009-05-29 07:15:00.000 2009-05-31 13:45:00.000
421 17 2009-06-18 04:52:00.000 2009-06-19 10:55:00.000
421 18 2009-06-30 13:39:00.000 2009-07-23 17:57:00.000
326 14 2010-06-07 11:35:00.000 2010-06-08 15:58:00.000
326 21 2010-07-01 17:41:00.000 2010-07-01 17:41:00.000
please help.
Thanks!