Group By Dates between start and end

[axios]

Hi, I need help in producing a report showing records grouped by indiviual day if that day is present between a start date and an end date on the record, for example:

4th Dec 2004
Rec ID Title Startdate Enddate
10 ABC 3rd Dec 2004 5th Dec 2004
11 ABCD 3rd Dec 2004 6th Dec 2004
12 ABCDE 1st Dec 2004 4th Dec 2004

5th Dec 2004
Rec ID Title Startdate Enddate
10 ABC 3rd Dec 2004 5th Dec 2004
11 ABCD 3rd Dec 2004 6th Dec 2004

6th Dec 2004
Rec ID Title Startdate Enddate
11 ABCD 3rd Dec 2004 6th Dec 2004

I cannot amend the database schema with another date table so must create the 'missing' dates between the start and end ones within Crystal to form the groups.

Can anyone give me a start here?

Crystal version is 8.5 and the database is SQL Server 2000

 

[Madawc]

You could include a wider range of data in your data, one that will include something for every date. Then suppress printing for unwanted records, showing just the group header or footer. This is an inefficient use of machine-time, but might be the simplest solution from your point of view.

Another way is to create running totals for all possible group-items. But with a date-range that would not work, unless it is always for a fixed period, say a week.

Much the best solution is a date table. Can't you ask for one to be set up? It will have a lot of uses, including identifying public holidays.

Madawc Williams (East Anglia, UK)

 

[axios]

I'm beginning to think the only way forward is to create a data table and include it as another datasource. Thanks for the tip.