2nd most recent date for record selection

[crogers111]

CR XI
SQL

I'm trying to create a formula to get a beginning date for a date range to use as part of a record selection criteria. The beginning date I need is 1 day after the 2nd most recent(maximum) date for a date field. For example.


Record ID Date
001 09/05/2007
002 09/13/2007
003 09/02/2007
004 09/13/2007


09/05/2007 is the 2nd most recent date so I need my beginning date for my range to be 09/06/2007

Can I do this as a formula or does this need to be a SQL expression. I've been trying to get a SQL expression using the following logic but am unsure if this is the correct approach and if so I'm having trouble with the syntax:

select max(date) from table where date < select max(date) from table

 

[synapsevampire]

Try:

select max(date) from (select date from table where date <> max(date))

I don't see how this might be used for a record selection though.

You might use a Command Object (listed as Add Command under your data source) to use a query as the source for the report's data, and then embed the criteria within the query itself to select the second mosyt recent date.

-k

 

[lbass]

You can create a command (database->database expert->your datasource->add command) like the following:

select max(table.`date`)
from table
where table.`date` < (select max(table.`date`) from table)

Then link the command on the date field to your table, using a < join from the command to the table and selecting "enforce both". Then you don't need to add it in your record selection.

If you mean to do this at a group level, then your command would like:

select max(table.`date`), table.`groupfield`
from table
where table.`date` < (select max(A.`date`) from table A where A.`groupfield` = table.`groupfield`)
Group by table.`groupfield`

Then you would link the command by the group field and use the date in your record selection formula, or use the < link and enforce both for the date, but an equal join for the group field.

-LB