Date Range help

[khekking]

Crystal 11
SQL

Disclosure: rookie with dates.

I have a sales force who visits customers. I want to query the customer activity 6-months prior to the month they visited, and then six months after the month they visited.

My prior to visit formula appears to be working.

If {Invoice_Detail.INVDTE_32} in dateserial(year({@last visit date}), month({@last visit date})-7,1) to
dateserial(year({@last visit date}), month({@last visit date})-1,1)-1
then {@Ext. Price}

However, my post visit formula is not.

if {Invoice_Detail.INVDTE_32} in dateserial(year({@last visit date}), month({@last visit date})+7,1) to
dateserial(year({@last visit date}), month({@last visit date})+1,1)-1
then {@Ext. Price}

Thanks in advance for your help. Also, can anyone point me to a good resource for understanding dates, their ranges, and the manipulation thereof.

 

[MCuthill]

khekking,

I think your formula's are perhaps better suited to using the "DateAdd" function in Crystal. This will negate issues which will arise from when you, for example, try to subtract 7 months from March (month 3) - the result being -4.

DateAdd(intervalType,nInvervals,startDateTime)

You will need 2x formula's - one for the "Date-in-past" flag as well as the "future-date" flag dates.

for example:

{@Flag_6MonthsPrior}
DateAdd("m",-6,{@Last Visit Date})

{@Flag_6MonthsFuture}
DateAdd("m",6,{@Last Visit Date})

These formulas can then be refences in the formulas you provided in order to get the first of the months. Please see "Help" in Crystal for the specifics of the DateAdd() Function.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

You have it backwards:

if {Invoice_Detail.INVDTE_32} in dateserial(year({@last visit date}), month({@last visit date})+1,1) to
dateserial(year({@last visit date}), month({@last visit date})+7,1)-1
then {@Ext. Price}

-LB

 

[khekking]

Thanks for the fix. I'm being forced to write more and more Crystal and I'm struggling with date issues. Does anyone have a resource, i.e., book, online tutorial, dvd, anything.... they would recommend so I can get a better handle on dates.

Thanks in advance for your suggestions. I'm in a small company and this is getting dumped on me and I am feeling overwhelmed and frankly a bit stupid with how much I'm struggling with understanding dates in Crystal.