How to get Latest Effective Record (Effective Date is not an option)

Summary:

The table I'm using does not have an effective date field but does contain a field called RUNDTTM which is composed of date and time. How do I use this field to get the latest effective row on this field?

How to get Latest Effective Record (Effective Date is not an option)

gsolorio 16 Feb 2010 18:59

I need help. I'm creating a PS Query in 9.0 and I have a table that contains a field called RUNDTTM. This field is composed of Date and Time. When I add this table it doesn't have an "Effective Date". So I can only use the RUNDTTM to include dates in my query results however I need the most current record (or the latest effective date). When I select the RUNDTTM, I click "Edit" and click the "Max" so that I get all the current records. But it is not. I even tried using an expression MAX (c.RUNDTTM) and added this to my "fields" tab and that doesn't work either (gives me an error). What can I do to get the latest effective record?

Last edited on 16 Feb 2010 19:02 by gsolorio Show more

Re: How to get Latest Effective Record (Effective Date is not an option)

Praj 25 Feb 2010 22:56

Hi,

You'll need to add a criteria in your main query for C.RUNDTTM with a condition type of equal to a subquery. Your subquery should be on the same table that C.RUNDTTM comes from with one field; RUNDTTM and the max aggregate function applied. Your subquery should also join on other key fields if appropriate.

So you should end up with SQL that looks like this (in the View SQL tab)

and C.RUNDTTM = (SELECT MAX(D.RUNDTTM) FROM YOUR_TABLE D WHERE D.KEY1 = C.KEY1 ... etc)