How to pull records from last full month excluding weekends 2

[willz99ta]

Hi and thanks for your help,

Is it possible to make a selection query that pulls records from last full month excluding weekends?

Thanks for the help,
Will

 

[fisheromacse]

i think this should work....
create a SQL expression to determine the day of the week like this:
{%dow} = {fn DAYOFWEEK("TABLENAME"."FIELDNAME")}

Then in the select statement use the above expression to have it choose any day of week that is not Saturday or Sunday (1 is Sunday and 7 is Saturday). Also in the select statement, use your date field and select 'in the period' then use the option "lastfullmonth" ...like this:
(NOT({%dow}=1) AND NOT({%dow}=7))
AND
TABLENAME.DATEFIELD in lastfullmonth

 

[willz99ta]

I did something very similar to Fish's suggestion. I made a formula variable called DayOfTheWeek.
Its formula read:
DayOfWeek({DATEFIELD})

I then put this in the selection record:
{@DayOfTheWeek} in [2,3,4,5,6]

It worked perfect. Thanks Fish.
Will

 

[lbass]

Will,

If you use the suggested SQL expression {%dow}, you will get better performance, as in:

{%dow} in 2 to 6 and
{Table.date} in lastfullmonth

-LB

 

[willz99ta]

How do I use an SQL query in my report. I know SQL, but have never used it in a Crystal Report.

Thanks,
Will

 

[lbass]

Create the SQL expression (field explorer->SQL expression->new) as shown in fisheromacse's post, using the field list to replace "TABLENAME"."FIELDNAME" with your actual field--this will ensure the correct punctuation for your datasource also. Then use the result in your record selection formula as shown in my post.

-LB