Dates 1

[brishanny]

I am pulling info from a sql2000 database and using CR9.0

I am using a order number and a date. I want to create a report showing how many days a product has been sitting in our plant after in was created. How can I use the date and get 30/60/90 days information?

I can create a formula for getting the number of days differance but can't figure out how to then break it up in groups by 30/60/90 days.

Any ideas

Order Number 30 Days
111-111 123.56
222-222 523.36

Order Number 60 Days
444-444 452.21
555-555 785.26

 

[bdreed35]

Create this formula and the Group on it:

@Aged

if {table.Order_Date} IN Aged0To30Days then
    "30 Days"
else if {table.Order_Date} IN Aged31To60Days then
    "60 Days"
else if {table.Order_Date} IN Aged61To90Days then
    "90 Days"
else
    "Over 90 Days"

~Brian

 

[brishanny]

Thanks for the Reply. It worked wonderfully. I am fairly new to crystal so I have a modification question to your response.

if {table.Order_Date} IN Aged0To30Days then
"30 Days"

If I change Aged0To30Days to Aged30To59Days then it errors. Is this a built in function? Thanks

 

[brishanny]

Thanks but I figured it out. I used the >= and <= to filter the data. I also noticed your function was built in. Thanks again.

 

[bdreed35]

Yes, Aged0To30Days is a built in function. You can work around it, though, using the DateDiff function to get the number of days and then make your own upper and lower limit.

if DateDiff("d",{table.Order_Date},CurrentDate) IN 0 to 29 then
    "30 Days"
else if DateDiff("d",{table.Order_Date},CurrentDate) IN 30 to 59 then
    "60 Days"
else if DateDiff("d",{table.Order_Date},CurrentDate) IN 60 to 89 then
    "90 Days"
else
    "Over 90 Days"

You can customize the upper and lower limits if you need to with this option

~Brian