SQLTeam.com | Weblogs | Forums

Display all the admissions happened in 30 days


#1

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!


#2

Try if the following query gives you what you need.

SELECT
	patient_id,
	admid,
	adm_date,
	dis_date
FROM
	admissions a1
WHERE
	EXISTS
	(
		SELECT *
		FROM
			admissions a2
		WHERE
			a2.patient_id = a1.patient_id
			AND a2.admid <> a1.admid
			AND 
			(
				ABS(DATEDIFF(d, a1.adm_date,a2.dis_date)) <= 30
				OR ABS(DATEDIFF(d, a2.adm_date,a1.dis_date)) <= 30
			)
	);

If that does not work, can you post consumable data? i.e., fill in the rest of the data after creating at test table like what I started below:

CREATE TABLE #admissions
( patient_id INT, admid INT, adm_date DATE, dis_date DATE);

INSERT INTO #admissions
        ( patient_id ,
          admid ,
      adm_date ,
      dis_date
    )
VALUES  ( 215,12,'20110517','20110519'),
		( 421,13,'20090512',';20090513')

#3

Yes, your suggestion worked :slight_smile: , Thank you so much for your time.