Return fields from the same record in a grouped query

[kballing]

Im working with a database of heart transplant catheter procedures on patients. I'm trying to get the exam information for a number of patients from their exam closest to a given date.

When a patient comes in for a regular checkup, they usually have a cath procedure done that day, but occasionally, due to scheduling issues, it may be postponed a week or two or if a procedure was done recently before their checkup. So there is a window of + or - 14 days. However, supposing their procedure is positive for some problem, they may have it repeated again within that window. e.g. patient X has a procedure the day after his checkup that shows a problem, after treatment he has a follow-up procedure 5 days later. I'm only interested in the one procedure closest to the checkup date.

I have a table,PatientDates of PatientIDs and CheckDates. The procedure database, Cath, has a fields: CathKey (procedure primary key), PatientID, CathDate, Result1, Result2, etc.

I need to return the CathKey for the CathDate closest to CheckDate for each PatientID.

essentially:
Select PatientID, first(CathKey)
From PatientDates Inner Join Cath on PatientDates.PatientID = Cath.PatientID
Where CathDate between CheckDate+14 and CheckDate-14
GroupBy PatientID, CathKey
Having abs(CathDate-CheckDate) = min(abs(CathDate-CheckDate));

This doesn't work because it's grouped by Cathkey, but if I use first(CathKey), it doesn't correspond the the closest date.

 

[jzwp22]

I think you are looking for something like this:


SELECT PatientDates.PatientID, patientDates.checkdates, (SELECT Top 1 CathKey FROM Cath WHERE Cath.CathDate>PatientDates.CheckDates and Cath.PatientID=PatientDates.patientID order by Cath.patientID, cath.cathdate asc) as CathKeyClosestToCheckDate
FROM PatientDates

 

[kballing]

Yes, that is the very simple solution I was looking for. However, just after I posted, I had a Eureka! moment and I came up with the same solution on my own.

I've done this before, I guess I was having a brain fart. I sat in front of my computer for 12 hours thinking up extremely complicated ways to fail.

 

[jzwp22]

I'm glad you were able to overcome the brain fart and get it worked out! Good luck with your project.

 

[Brianwarnock]

I guess I'm missing something but isn't this going to find the soonest after the procedure rather than the closest which might be before the procedure?

Brian