Select Expert - Date in the period Last 12 Months 2

[JCook23]

I need to have a date always be in the period of the last full year. If I run it on 9/30/04 it includes all ship dates since 10/1/03. I did not see this in the list of periods within the Select Expert. Help needed please....

CR 10.0
SqlServer

Jeff C.

 

[synapsevampire]

The Select Expert is a wizard, use the manual means:

Report->Edit Selection Formula->Record:

{table.date} >= dateadd("y",-1,currentdate-1)
and
{table.date} <= currentdate-1

Note that you may find differences in the menus in my example and in your version of Crystal, this is because you omitted basic information, such as your version of Crystal.

-k

 

[JCook23]

k,

This only gives me the last two days. Is there something I am missing?

Thanks for all your help....

Jeff C.

 

[mbarron]

k,

Would rearranging your formula as

{table.date} in dateadd("y",-1,currentdate-1) to
currentdate-1

have any affect on performance?

Unfortunately, I can't test against my own data. Dates are stored as numbers.



Mike

 

[synapsevampire]

Well performance is an entirely different issue.

Sorry, I always forget:

{table.date} >= dateadd("yyyy",-1,currentdate-1)
and
{table.date} <= currentdate-1

Need the 4 Y's for year.

-k

 

[mbarron]

This should work.

{table.date} >= dateadd("yyyy",-1,currentdate-1)
and
{table.date} <= currentdate-1



Mike

 

[synapsevampire]

Read my FAQ for optimizing the SQL (check Database->Show SQL Query, it should reflect the dates for optimal performance):

faq767-3825

You'd use 2 date formulas and reference them in the record selection formula.

-k

 

[JCook23]

Thanks so much for your help.....I truly appreciate it.