Can LastFullMonth be pushed down to database server

[DonnaB]

I'm just wondering if LastFullMonth can be pushed down to the database server when using it in the record selection, or do I have to write on optimised version of it in SQL?

 

[kenhamady]

I don't think so-since it is a CR function and CR functions don't usually make to the SQL. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[AndrewBaines]

In SQL 2000 and some other databases you can create userdefined functions. I've never tried it, but you could use this to define a server side function that did lastFullMonth. Andrew Baines
Chase International

 

[DonnaB]

Thanks for that guys.

I don't really have much to do with the SQL side of things though, I'm just trying to optimise my Crystal functions as much as possible to speed up processing time.

 

[synapsevampire]

It can pass it on my Oracle database, I just tested it.

{SOL_VW_RPT_CONTACT_DTL.UTCSTARTTIME} in lastfullmonth

will net:

WHERE
"SOL_VW_RPT_CONTACT_DTL"."UTCSTARTTIME" >= TO_DATE ('01-05-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
&quot;SOL_VW_RPT_CONTACT_DTL&quot;.&quot;UTCSTARTTIME&quot; <= TO_DATE ('31-05-2002 23:59:59', 'DD-MM-YYYY HH24:MI:SS')

Post your entire record selection criteria and let's have a go at it.

Or, you might create start and end date formulas as in the following:

(note I use parameters to drive this so the user can select lastfullmonth, lastfullyear, etc, here's the lastfullmonth)

@startdate
If {?Predefined Date Range} = &quot;Last Month&quot; Then
If Month (CurrentDate) = 1 Then
DateTime((Year(CurrentDate)-1),12,1,0,0,0)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)-1,1,0,0,0)

@enddate
If {?Predefined Date Range} = &quot;Last Month&quot; Then
If Month (CurrentDate) = 1 Then
DateTime(Year(CurrentDate)-1,12,1,23,59,59)
Else
DateTime(Year(CurrentDate),Month(CurrentDate),1,23,59,59)-1

record selection criteria:

(
({YourTable.YourDate} >= {@Date Range Start})
and
({YourTable.YourDate} <= {@Date Range End})
)

Note the parens.

My requirements and record selection criteria are much more elaborate than this, and I ALWAYS get all of the SQL to pass.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[computek353]

Lastfullmonth works fine in my record selection formula too. You shouldn't have any problems using it in there.

 

[dgillz]

Computek-

The issue is weather you can use it in a record selection formula, it weather it can be passed to and processed on the server, for performance considerations. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com