Need to group records in 2 week intervals from start date to end date 1

[scoob28]

I've read through the threads but just can't get my head around what I need to do.

I have several hundreds records in a table that each have a temperature and date attribute. I want to use parameter fields to allow the user to enter a start date and an end date. I then want to display an average of all of the records between those two dates grouped in two week intervals. The date I need to display for the two week interval is the end date of that interval.

So if the user enters 1/15/09 as a start date I'd like the first grouping to be an average of all the temperature records between 1/15/09 and 1/29/09 with 1/29/09 as the "label".

I'm using CR XI.

Thanks.

 

[briangriffin]

Your best bet is probably to create a database table with a record for each date from the earliest record date through the next ten years or so. You would then a column for week ending date, that resets every two weeks:

1/1/2009 1/14/2009
1/2/2009 1/14/2009
1/3/2009 1/14/2009
....
1/15/2009 1/29/2009
etc.

Then link to the table and group on the week ending date. The advantage is that it's reusable, and would require very little report manipulation. You can also add additional columns for different purposes - for instance, I've done this with a column for pay period end date, and actual pay date.

The downside is if you require the two week range to be dynamic - that is, if the user enters 1/1 as the start date then you want 1/14 as the w/e date, but if they enter 1/2 as the start date then you want 1/15 as the w/e date. This approach won't accomodate that, although it could be done as a command so that the table generates each time the report is run. Depends on your database and your access to it.

 

[scoob28]

Thanks for the reply Brian.

I'm hoping to do this without creating an additional table. The two week range would not have to be dynamic.

I was trying to create a formula based on the inputed dates and group on that formula but wasn't getting anywhere.

 

[lbass]

Use a record selection formula to limit records to your range:

{table.date} >= {?start} and
{table.date} < {?end} + 1

Then insert a group on {table.date} and choose "print for each two weeks"->options->customize group name->use a formula->x+2 and enter:

{table.date}-dayofweek({table.date})+14

This assumes you want the two-week intervals to run Sunday to Saturday.

-LB

 

[scoob28]

Thanks LB. This is exactly what I was looking for.