Date selection Formula or SQL help

[Rbp1226]

Hello,

I am currently using Crystal Report 2008 and oracle 10g; I am trying to find either Report record selection formula or SQL selection to get Date records back to 5 years. For example:

Training records:


Course_Title Date_Completed Person_ID
Network 1/3/2009 1
System 8/22/2009 1
Laptop 3/23/2008 1
Desktop 2/18/2007 1
C++ 2/5/2007 1
Java 2/3/2004 1
C 8/2/2004 1

The report should show all records except record Java, because the completing of Date is out of 5 years range, is there anyone have good solution?

Thanks!!

Rbp1226

 

[fisheromacse]

in your select expert set the date to greater than or equal to:

dateadd("yyyy",-5,currentdate)

 

[Rbp1226]

Fisheromacse, thanks for your help, sorry I didn't explain clear, it is not based on currentdate, it based on each persons records to get max date, it should be like:

dateadd('yyyy', -5, max(date_completed),

I tried few different ways, did not work well. Is there any one have good idea.

Thanks,

Rbp1226

 

[Rbp1226]

update:

dateadd('yyyy', -5, max(date_completed))

Thanks,

Rbp1226

 

[GJParker]

In your report you will need to have a group on person id and then add a formula into the group selection formula i.e.

{table.date_completed} >= Maximum({table.date_completed}, {table.person_id}) - 1825 //(5 years in days)

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

You could create a SQL expression {%maxcompl}:

(
select max(A."Date_Completed")
from "table" A
where A."Person_ID" = table."Person_ID"
)

Then you could go to report->selection formula->RECORD and enter:

{table.date} >= dateadd("yyyy",-5,{%maxcompl})

-LB

 

[Rbp1226]

lbass,
It works Great. Thank you very mcuh!!

May I know any other idea if I use universe as data source which SQL expression not available.

Thanks!!


Rbp1226

 

[lbass]

I don't know anything about universes. Sorry. You can add the equivalent of SQL expressions into commands as subqueries in the Select statement.

-LB