Help with record selection formula - dates

[Tobiet]

Hi:

Thanks in advance for your help from a relatively inexperienced user. I am using Crystal v10 via OBDC connection to SQL server.

I am trying to write a formula selecting sales for a product for the prior three complete calendar months. For example, if the report is run anytime in January, I need all sales for the full months of October, November and December. Run anytime in February, sales for November, December and January.

I have a formula which does work but seems to be very clunky.

if month(currentdate) - month({InvOutDist.DELIVERY_DATE}) in [-11,-10,-9] then

month(currentdate) - month({InvOutDist.DELIVERY_DATE}) in [-11,-10,-9] and
year(currentdate) - year({InvOutDist.DELIVERY_DATE}) in [1])

else

month(currentdate) - month({InvOutDist.DELIVERY_DATE}) in [1,2,3] and
year(currentdate) - year({InvOutDist.DELIVERY_DATE}) in [0])

Is there an easier, smoother way to do this?

Thanks.

 

[mbarron]

Using the following in the select expert will work. Your performance (speed) may suffer.

datediff("m",{InvOutDist.DELIVERY_DATE},currentdate) in 1 to 3

Mike

 

[kskid]

If you want the last three months of data, then use this in your record selection

{table.datefield} in [
dateserial(year(currentdate)),month(currentdate)-3,1) to_
dateserial(year(currentdate),month(currentdate),1)
]

I like date serial because it automatically adjusts the year if the months overlap from one year to the next.

Also note that I have to_ instead of to. It means to exclude the last date, which translates to the 1st day of the current month.

You can look at Database -> Show SQL Query to verify selection.

Cheers,

-LW

 

[Tobiet]

Thank you both for your responses giving me two different approaches!