Grouping dates

[pritiw]

Hi All,
I have a report which takes a date range as parameter. I have a date field say enteredtimestamp in my database table. Now if say for example the user enters a start date as 04/18/2002 and end date as 05/20/2002, than i need to get all records for which enteredtimestamp is within this range. I can do that but further i need to group these records accorging to weeks.
The first group should be:
04/18/2002 to 04/24/2002
second group :
04/25/2002 to 05/01/2002
and so on
I know if igroup my report by enteredtimestamp and than select to print it for each week, the first group comes out to be : 04/14/2002 to 04/20/2002 and so on....
I hope i have made myself clear.
Thanks for the help
Priti

 

[chelseatech]

Sure, Cr groups by week and starts the week on a Sunday. You want a weekly grouping starting from the first date of your selected date range.

So you can't group by the date, you will need to group by a formula.

Start of Date Range:
Minimum({?Date Range})

WeekNo:
truncate(({table.datefield} - {@Start of Date Range})/7,0)

DateGroup:
{@Start of Date Range} + (WeekNo - 1) * 7

And group by this final formula. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm