Hi, Given a table like define_table(‘person’,Field(‘EmployeeID’),Field(‘name’),Field(‘effdt’,type=’date’)) And having a persons name changed on a specific date:
|1| 1234 | G. Single | 2008-01-01 | |2| 1234 | G. Single-Married | 2012-08-01 | |3| 1234 | G. Single | 2015-05-01 | |4| 5678 | S. Else | 2006-01-01 | I want the current names. We use effective dated queries for this: Select p.EmployeeID, p.name from person p Where p.effdt = (select max(p_ed.effdt) from person p_ed where p.employeeid = p_ed.employeeid and p_ed.effdt <= sysdate) Which will return me two rows (2 and 4 in this example - one row per person with their current name) with only one roundtrip to the database. I just can’t figure out how to rewrite this query to DAL and I would really like to. Any help is appreciated, thank you. With kind regards, Remco Boerma --
