Crystal Select Expert formula? 3

[billfan]

I haven't gotten my tutorial books in the mail yet so I'm going this blindly. Great thanks to anyone who can help.

I'm trying to pull the number of visits we had to a certain clinic of ours for last year, but I only want Wednesdays. i don't know how to arrange the select expert so that it only pulls the visit data from each Wednesday in 2004 instead of just taking the whole date range from January to December. I'm not even sure if I need a formula. Here's the formula it shows when I just wanted the whole time period of 2004:
{dldata_charge.CHG_DATE} in DateTime (2004, 01, 01, 00, 00, 00) to DateTime (2004, 12, 31, 00, 00, 00)

Do I just modify this formula in some way or is there another way that I'm totally missing? Thanks for the help.

 

[mbarron]

The following will bring back all Wednesdays for the range of Jan 1, 2004 to Dec 31, 2004.

{dldata_charge.CHG_DATE} in DateTime (2004, 01, 01, 00, 00, 00) to DateTime (2004, 12, 31, 00, 00, 00)
and
dayofweek({dldata_charge.CHG_DATE})=4

Another option would be:
year({dldata_charge.CHG_DATE}) =2004 and dayofweek({dldata_charge.CHG_DATE})=4

Mike

 

[lbass]

I would change this to:

{dldata_charge.CHG_DATE} in DateTime (2004, 01, 01, 00, 00, 00) to DateTime (2004, 12, 31, 23, 59, 59) and
dayofweek({dldata_charge.CHG_DATE}) = 4

Note that time(0,0,0) denotes midnight, so you would lose the last day of the year unless you change the time elements as noted above.

-LB

 

[bdreed35]

A couple of thoughts with this.

Another way to do the dates is the change the formula to use date functions rather than datetime:

{dldata_charge.CHG_DATE} in Date(2004, 01, 01) to Date(2004, 12, 31)

This gets transalated in the SQL Where clause as:
WHERE (`dldata_charge`.`CHG_DATE`>={ts '2001-01-01 00:00:00'} AND `dldata_charge`.`CHG_DATE`<{ts '2002-01-01 00:00:00'})

My second thought is that using the dayofweek function will not get passed down to the database, creating a potentially inefficient report. A better solution would be to use a SQL Expression, if it is available with the database driver you are using.
Against the Xtreme Sample database, I was able to create a SQL Expression that used the DayOfWeek function. I then used the SQL Expression in the Selection formula which would look like this:

{dldata_charge.CHG_DATE} in Date(2004, 01, 01) to Date(2004, 12, 31) and
{%DayOfWeek} = 4


~Brian