I need to find the test Result for the Specimen that was Collected closest to but not after a blood TransfusionDateTime.
Patients can have multiple SpecimenCollectionDateTimes.
SpecimenCollectionDateTimes can have multiple Tests.
TransfusionDateTimes can have multiple blood Units.
We want to track Test Results for each blood Unit (not for each TransfusionDateTime).
I've calculated the [Hours Difference] between each TransfusionDateTime and each SpecimenCollectionDateTime.
And using Peek and Order By, I've told it to add a count to the SpecimenCollectionDateTime starting with "-1" for the last one before the transfusion and subtracting 1 for each prior but it doesn't always work (I also attached a .qvw):
TempTestTimes:
LOAD [Account Number] as TempAccountNumber
,[Transfusion DTTM] as TempTransfusionDTTM
Resident TransfusedUnits;
JOIN
LOAD [Account Number] as TempAccountNumber
,[TestName] as TempTestName
,[Specimen Collection DTTM] as TempSpecCollectionDTTM
,[Test Result] as TempTestResult
Resident LabData;
TempTestTimeDifferences:
LOAD
RecNo() as TempRecNo,
TempAccountNumber,
TempTransfusionDTTM,
TempTestName,
TempSpecCollectionDTTM,
INTERVAL(TempSpecCollectionDTTM - TempTransfusionDTTM, 'hh:mm') as [Hours Difference]
,TempTestResult
Resident TempTestTimes
WHERE TempSpecCollectionDTTM < TempTransfusionDTTM
;
JOIN (TempTestTimeDifferences)
LOAD TempRecNo
,If(
(TempAccountNumber = previous(TempAccountNumber)
AND TempTransfusionDTTM = previous(TempTransfusionDTTM)
AND TempSpecCollectionDTTM = previous(TempSpecCollectionDTTM)
AND TempTestName = previous(TempTestName)
AND [Hours Difference] = previous([Hours Difference])
AND [Hours Difference] < 0
), peek([Hours Difference Rank])-1,-1) as [Hours Difference Rank]
Resident TempTestTimeDifferences
//Where [Hours Difference] < 0
Order By TempAccountNumber, TempTransfusionDTTM, TempSpecCollectionDTTM, TempTestName,UnitNumber,
[Hours Difference] desc
;
One scenario that proved difficult was when some blood tests (Hemoglobin, Platelets, INR) were done on multiple specimens (each with their own SpecimenCollectionDateTime) and some were only done on one.
