need advise on this complex sql

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

need advise on this complex sql

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2007-05-06 : 19:34:28

Hi friends
i've 2 tables Patient and booking.1 to many relationship. booking table contains an entry every time a patient visits clinic.
How can I write a query so that it returns only patients that have been to the clinic before year 2001 and haven’t come since. In other words haven’t come back in 7 years.

btw both tables linked thru "patientid" and booking table has "booked date"
Thanks for ur advise

Cheers

hey001us
Posting Yak Master

185 Posts

Posted - 2007-05-06 : 22:05:19

SELECT p.patientid FROM Patient p
WHERE p.patientid NOT IN (SELECT p.patientid FROM booking WHERE visitdate >= DATEADD(yy,-7,GETDATE()))

hey

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2007-05-06 : 22:07:54

Thanks Hey.
Finally thats what i managed to do. Thanks.

Cheers

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-06 : 22:14:20

that query doesn't work if the patient never had an appointment.

EDIT: that is, if you have rows in the patient table with no associated rows in the booking table, they will be returned by the query. but your requirement says you want patients that had an appointment before 2001, but not after.

here's one way you could do it:


select p.PatientID 
from patient p
join booking b on p.PatientID=b.PatientID
left join (select p2.PatientID 
           from patient p2
           join booking b2 on p2.PatientID=b2.PatientID
           where b2.BookedDate > '20010101') a -- derived table for patients that had an appointment after 2001/01/01
on a.PatientID=p.PatientID
where a.PatientID is null

www.elsasoft.org

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2007-05-06 : 22:19:53

Jezemine
i did that slightly differently.

SELECT p.patientid FROM Patient p left outer join booking b on p.patientid= b.patientid
WHERE p.patientid NOT IN (SELECT p.patientid FROM booking WHERE visitdate >= DATEADD(yy,-7,GETDATE()))

Cheers

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-06 : 22:27:30

Your query will still return patients that never had an appointment:


declare @patient table(PatientID int)
declare @booking table(PatientID int, BookedDate datetime)

insert into @patient 
select 1 union all 
select 2 union all 
select 3 union all 
select 4 union all 
select 5 

insert into @booking
select 1, '20070101' union all 
select 1, '20000101' union all
select 2, '20000101' union all
select 2, '20000201' union all
select 2, '20000301' union all
select 2, '20000401' union all
select 3, '20020101' 

-- yours, returns patients 2,4,5
SELECT p.PatientID FROM @patient p left outer join @booking b on p.PatientID=b.PatientID
WHERE p.PatientID NOT IN (SELECT PatientID FROM @booking WHERE BookedDate > '20010101')

-- mine, returns 2 only (4 and 5 never had appts)
select p.PatientID 
from @patient p
join @booking b on p.PatientID=b.PatientID
left join (select p2.PatientID 
           from @patient p2
           join @booking b2 on p2.PatientID=b2.PatientID
           where b2.BookedDate > '20010101') a -- derived table for patients that had an appointment after 2001/01/01
on a.PatientID=p.PatientID
where a.PatientID is null

www.elsasoft.org

hey001us
Posting Yak Master

185 Posts

Posted - 2007-05-06 : 22:29:14

Those also consider as not visited from past 7 years

But you can this way :P

SELECT p.patientid #TEMP FROM Patient p
WHERE p.patientid NOT IN (SELECT p.patientid FROM booking WHERE visitdate >= DATEADD(yy,-7,GETDATE()))

SELECT * FROM #TEMP WHERE patientid IN (SELECT patientid FROM booking)

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 03:22:06

Try this. Sometimes just lean back and think about what you are trying to achieve.

SELECT		PatientID
FROM		Booking
GROUP BY	PatientID
HAVING		MAX(VisitDate) < '20010101'

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 03:25:43

Using jezemine's test data above

-- Peso's suggestion
SELECT		p.*		
FROM		@Patient AS p
INNER JOIN	(
			SELECT		PatientID
			FROM		@Booking
			GROUP BY	PatientID
			HAVING		MAX(BookedDate) < '20010101'
		) AS b ON b.PatientID = p.PatientID

Peter Larsson
Helsingborg, Sweden

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2007-05-07 : 16:43:06

Thank you all for ur advise and help.much appreciated.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources