Selecting record with date nearest todays

[Prisk]

Does anyone out there know how to do this?

I have two records for the same company and I want to select the record that has an application date nearest to the current date, is there a formula that I can write to do this??

Thank you

 

[beltmanjr]

maximum({dateField}) will give you the highest date available.

However, if your date can be in both past and future use the something like:

numberVar datediff1 := DateDiff ("d", {dateField}, currentdate());
numberVar datediff2 := DateDiff ("d", next({dateField}), currentdate())

if (datediff2 < datediff1) then
   datediff2 
else
   datediff21

The main thing is the structure of these datefields. How do they display in your report? and in your database?

 

[Prisk]

The date fields are not displayed just the details of the most recent application, the dates can be both in the past and future, and there can be any number of them

 

[beltmanjr]

Ok, I would always display the field in question in my draft report versions to visiually check.

Anyway, can you give sample data of your table?
Is it only 1 table you look at with something like

Sample company 20070101
Sample company 20071118
Sample company 20071121

???

 

[Prisk]

OK, the report looks like -

Company Name Reg Status Reg Type Reg Date
Company 1 Active ACT 12/11/2007
Company 1 Active SCT 09/09/2007
Company 1 resigned ACT 06/06/2007

And I need to just display information for the first line (on the 12/11/2007), however this field can also be null!

Thanks for your help with this

 

[beltmanjr]

In sql (MSSQL in this case)

select min(abs(DATEDIFF(DAY, datum, getdate()))) from dbo.Table1

datum is your table field.

You could try in the selection formula (I would try group) under Report:

minimum(abs(DATEDIFF(d, {datum}, currentdate()))) = {datum}

 

[Prisk]

Thanks, I'll try it now

 

[lbass]

Another way:

Insert a group on company. Then create a formula {@diff}:

abs(datediff("d",{table.date},currentdate))

Then go to report->selection formula->GROUP and enter:

{@diff} = minimum({@diff},{table.company})

-LB

 

[beltmanjr]

lbass, would that not take the date selection out of the sql query since it uses a formula?

 

[lbass]

The group selection won't pass regardless. I don't think you can use a minimum wrapped around abs() wrapped around datediff()--at least not within CR. I didn't test it in a command.

-LB