How to Group Data on range of values.

[Rissole]

Hey ...

I've this following table ...

PeriodID StartingDate EndingDate

May02 03/May/2002 05/Jun/2002
Jun02 06/Jun/2002 30/Jun/2002
Jul02 01/Jul/2002 02/Aug/2002

The table manifestly showing the start and end dates for May 2002, June 2002 & July 2002.

I want to group the data on these date ranges (ofcourse not this table, but some other table with a date field and not the PeriodID).

Thanks in Advance,
Shuja.

 

[rci]

Rissole,

Unless you can get someone to add the period as a column on the "other" table, you'll need to create a formula in CR that defines the periods, then group on that formula.

For example, you could create a formula called PeriodGroups that has the following code:

if {table.date} >= date (2002, 5, 2)
and {table.date} <= date (2002, 6, 5)
then &quot;May 02&quot;
else if {table.date} >= date (2002, 6, 6)
and {table.date} <= date (2002, 6, 30)
then &quot;Jun 02&quot;
else if {table.date} >= date (2002, 7, 1)
and {table.date} <= date (2002, 8, 2)
then &quot;Jul 02&quot;
...

Group the report on PeriodGroups.

Not very elegant, but it'll get the job done.

 

[rci]

One more note: you should pick something that sorts better than what I showed in my formula. Those groups (&quot;May 02&quot;, &quot;Jun 02&quot;, etc.) won't sort in date order.

 

[JohnEck]

Or if you can create a table with these collumns

Period BegDate EndDate
------ -------- --------
May02 05/03/02 05/30/02
Jun02 05/31/02 06/27/02
Jul02 06/28/02 07/25/02

Then join the table to you data using this statement
DataTbl INNER JOIN DatesTbl ON (DataDate > = BegDate
AND DataDate <= EndDate)

This is allows you to use the table for multiple reports without having to reuse the formula and much easier to update next year when the ne fiscal calendar starts.

 

[SteveRemington]

Another way to do this it create a Date Dimension Table, similar to those used in data warehouses.

The table might look something like this:

[tt]
Date e.g. 03-May-2002
PeriodId e.g. 200202
PeriodDesc e.g. May-2002
QuarterId e.g. 2002Q1
QuarterDesc e.g. Jul-Sep 2002
.
.
.
[/tt]

There would be one entry in this table for each day of the year for each year. Doing it this way avoids using Less or greater than type SQL joins which can be slow and also provides the opportunity to add other functionality like the number of hours worked per day, whether the day is weekend or week day, etc.

You would join you data table to the dimension table via the relevant date fields.