Date range logic CR7

[bxgirl]

Using CR7, mysql 4

I have a report that list customer packages based an a date range that a user provides. Customer packages have a BeginDate and EndDate which indicates when the package is valid.

Ex. user provides the following date range to run the report:

Start Date: 9/1/05
End Date: 12/1/05

I have customer packages with BeginDate = 8/1/04 and EndDate of 10/31/05. I need to then display the month within the date range the package is good for. For this example I need to show the package is good for Sept 05 and Oct 05. How can I accomplish this in crystal?

 

[GJParker]

This is quiet possible in CR, but how would youo like to display the data ?

Please provide a sample of your input data and the expected output of this same data.



Gary Parker
MIS Data Analyst
Manchester, England

 

[bxgirl]

The data is to be displayed as follows in a simple table format:

1. CustomerName
2. PackageName
3. The month the package is good for within the given date range

 

[lbass]

You would have to establish the rule about whether the month should be included if any date falls within that month. In the following, I assumed that the presence of a start or end date within the parameter period or the month would result in the inclusion of that month. For the record selection formula, use something like:

{table.startdate} <= {?end} and
{table.enddate} >= {?begin}

Then create a detail level formula like:

numbervar i;
stringvar x;

for i := 1 to 12 do(
if {table.startdate} <= dateserial(2006,i+1,1)-1 and
{table.enddate} >= date(2006,i,1) then
x := x + monthname(i) + ", ";
if len(x) >= 2 then
left(x,len(x)-2) else
x

-LB

 

[bxgirl]

Does crystal 7 support "for" loops.

 

[lbass]

I don't know--never had 7.0. Did you try the formula? If so, what message did you get?

-LB

 

[bxgirl]

CR 7 doestn't support for loops. Any other suggestion.

Thanks.

 

[lbass]

You could set up the formula to contain the largest potential range of dates. The following shows months for 2005, with a manual iteration.

stringvar y := "";

if {table.begindate} <= dateserial(2005,1,31) and
{table.enddate} >= date(2005,1,1) then
if not (monthname(1) in y) then
y := y + monthname(1) + ", ";
if {table.begindate} <= dateserial(2005,2,28) and
{table.enddate} >= date(2005,2,1) then
if not (monthname(2) in y) then
y := y + monthname(2) + ", ";
if {table.begindate} <= dateserial(2005,3,31) and
{table.enddate} >= date(2005,3,1) then
if not (monthname(3) in y) then
y := y + monthname(3) + ", ";
if {table.begindate} <= dateserial(2005,4,30) and
{table.enddate} >= date(2005,4,1) then
if not (monthname(4) in y) then
y := y + monthname(4) + ", ";
if {table.begindate} <= dateserial(2005,5,31) and
{table.enddate} >= date(2005,5,1) then
if not (monthname(5) in y) then
y := y + monthname(5) + ", ";
if {table.begindate} <= dateserial(2005,6,30) and
{table.enddate} >= date(2005,6,1) then
if not (monthname(6) in y) then
y := y + monthname(6) + ", ";
if {table.begindate} <= dateserial(2005,7,31) and
{table.enddate} >= date(2005,7,1) then
if not (monthname(7) in y) then
y := y + monthname(7) + ", ";
if {table.begindate} <= dateserial(2005,8,31) and
{table.enddate} >= date(2005,8,1) then
if not (monthname(8) in y) then
y := y + monthname(8) + ", ";
if {table.begindate} <= dateserial(2005,9,30) and
{table.enddate} >= date(2005,9,1) then
if not (monthname(9) in y) then
y := y + monthname(9) + ", ";
if {table.begindate} <= dateserial(2005,10,31) and
{table.enddate} >= date(2005,10,1) then
if not (monthname(10) in y) then
y := y + monthname(10) + ", ";
if {table.begindate} <= dateserial(2005,11,31) and
{table.enddate} >= date(2005,11,1) then
if not (monthname(11) in y) then
y := y + monthname(11) + ", ";
if {table.begindate} <= dateserial(2005,12,31) and
{table.enddate} >= date(2005,12,1) then
if not (monthname(12) in y) then
y := y + monthname(12) + ", ";
if len >= 2 then
left(y,len-2) else
y

-LB

 

[bxgirl]

I'll try that.

 

[bxgirl]

This will work if the parameter period falls within the year. It's possible to have a parameter period that spans two years.

 

[lbass]

Then you just need to build in two years worth of clauses in the if-thens.

-LB