CR9 Report based on "months open" SQL expression

[mouser4069]

I am attempting to write a SQL expression so that I can group results in a report by "months open," i.e., 0-6 months, 6-12 months, etc. I am getting an error when I attempt to save this. Any suggestions what I am doing incorrectly? Somone gave me this SQL expression, but it's not working:

MonthsOpen
{fn ROUND((SYSDATE - "FileHst"."DateStatus")/30,0)}

MonthsCategory
if MonthsOpen <= 6 then "0-6 Months"
else
if MonthsOpen > 6 and {@Months Open} <= 12 then "7-12 Months"
else
if MonthsOpen > 12 and {@Months Open} <= 24 then "13-24 Months"
else
"Over 24 Months"

MonthsCatSort
If MonthsOpen < 7 then 1
Else
If MonthsOpen < 13 then 2
Else
If MonthsOpens < 25 then 3
Else
4

 

[synapsevampire]

When do you get the error, and what is the error?

-k

 

[Madawc]

Is there some particular reason to do it in SQL? In Crystal it should be easy, maybe

DateDiff ("m", {FileHst.DateStatus},  currentdate)

Worth displaying 'Months Open' on the detail line before using it to group, just to confirm it is OK

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10