quote:

---

i just want retrieve patient information from 1st discharge date to 6 months later date .

---

-- *** Test Data ***
CREATE TABLE #t
(
	patnt_no int NOT NULL
	,patnt_refno int NOT NULL
	,admit_date datetime NOT NULL
	,discharge_date datetime NOT NULL
);
INSERT INTO #t
VALUES (2342, 5623, '20100507 12:00:00.000', '20100619 18:00:00.000')
	,(2342, 3562, '20100721 14:25:00.000', '20100925 07:55:16.000')
	,(2342, 7856, '20101115 00:00:00.000', '20130101 00:00:00.000')
	,(4261, 9567, '20110316 00:00:00.000', '20110526 12:00:00.000')
	,(4261, 7589, '20140415 00:00:00.000', '20140530 15:00:45.000')
	,(7689, 1254, '20120316 13:15:00.000', '20120428 16:00:00.000')
	,(7689, 4521, '20120714 15:00:00.000', '20120911 18:00:00.000')
	,(7689, 2642, '20120915 14:00:00.000', '20140413 16:00:00.000')
	,(5621, 2323, '20121012 13:00:15.000', '20140423 19:00:00.000')
	,(5621, 2423, '20140428 19:00:00.000', '20140505 16:00:00.000');
-- *** End Test Data ***
	
SELECT *
FROM #t T1
WHERE EXISTS
(
	SELECT 1
	FROM #t T2
	WHERE T2.patnt_no = T1.patnt_no
		AND T2.discharge_date < T1.discharge_date
		AND T2.discharge_date >= DATEADD(month, -6, T1.discharge_date) 
);