SQL Expressions for Date Range

[SJMcAbney]

I've been using Crystal Reports for a while now and some of my reports, as far as I have been able, have been reduced to run as fast as possible. They are, sometimes, still too slow.

I have since heard that SQL Expressions can reduce the time a report takes to run. With that in mind I would like to run a test report that selects a date range as specified by two parameters ({?Startdate} and {?Enddate}) by using SQL Expressions.

I've never used SQL (other than in Access) so I don't really know how to structure a SQL Expression in Crystal Reports. I've tried Googling about and can't find any basic examples of doing a date range in this way.

Assuming the date field, from tblExample, is called TestDate, can anyone offer a simple, step by step, example of creating a SQL expression that selects all records based on the dates entered in the aforementioned parameters?


Stewart J. McAbney | Talk History

 

[TheBlondOne]

Hi there,

I think the test which you are doing based on parameters is the wrong test to use for SQL expressions as parameter values are generally passed through to the database anyway (you can check this in the 'show SQL query').

One test to use which is what I usually show people is to create a crystal fomula which extracts the year from a date i.e

year({orders.order date})

then in the select expert set the formula to be equal to 2000

run the report and then look show SQL query. You will notice that this formula is not being passed to the database so crystal is having to check each record individually that is being brought back from the database to check to see if the year = 2000.


Now create a sql expression like this

year(`Orders`.`Order Date`)

Now remove the first formula from the select expert and put this one in and again set the SQL expression formula to = 2000.

Run the report and then look at the sql (Show SQL Query) you will then see that this is being passed to the database so that database is doing all the work and not crystal.

Also try looking through the help as there are some good tips in there on why and when to use them.

HTH


-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[lbass]

You can't use parameters in SQL expressions, as far as I know. Further, I think SQL expressions increase speed only in cases where the selection criteria are not all being passed to the SQL query. Often this happens because formulas are being used in the criteria or a function is being used to convert a field in the selection statement.

-LB