May 26, 2010 at 7:38 am
I have a primary key in the data pipeline called Row.PatientID. I need a way to lookup the max date from a juntion table based on this Row.PatientID but heres the catch, the only table I can do this in is a junction table which has multiple records with the same PatientID. Is there a way to select the row with the max date and pass in the Row.PatientID as a parameter in a lookup transform? If not is there some other transform/strategy I can use to do this?
May 26, 2010 at 7:54 am
Not 100% sure what you need, but maybe you could use the following query in the configuration of the lookup component:
SELECT
Patient_ID,
MAX(Date_Column) AS Date_Column
FROM My_Lookup_Table
GROUP BY Patient_ID
This code will make sure that your lookup table will have only 1 row for each patient_id, with the maximum date.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 26, 2010 at 10:40 am
I would tend to agree with the lookup method. It would likely be much more efficient than a one-by-one lookup.
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply