repeating records spanning multiple days

[sllynch]

I'm using Crystal Reports 8.5 with a SQL Server database. I've got a table containing meetings data; fields are "start date", "end date", "meeting name". The "start date" and "end date" fields are date/time fields. Most of the records have start and end dates that fall within a single day; there are some, though, that span multple days.

Sample data:

startdate: 06/21/2004 09:00:00
enddate: 06/21/2004 11:00:00
meetingname: First Session

startdate: 06/22/2004 14:00:00
enddate: 06/22/2004 16:00:00
meetingname: Afternoon Session

startdate: 06/21/2004 08:00:00
enddate: 06/23/2004 17:00:00
meetingname: Multi-Day All-Day Session

I'd like to set up a "daily report" grouped by day, that lists all the meetings occurring on each day - and for meetings that span multiple days, I'd like those entries to appear at the top under each day that they occur. Using the sample above, for instance, I'd like the report to look like this:

06/21/2004 Meetings:
Multi-Day All-Day Session
09:00 - 11:00 First Session

06/22/2004 Meetings:
Multi-Day All-Day Session
14:00 - 16:00 Afternoon Session

06/23/2004 Meetings:
Multi-Day All-Day Session



Can this be done?

 

[lyanch]

You need to do this from the data side by starting with dates.. and then select for each date the sessions that occur for the date, probably in a stored procedure or view.

In the were clause you would have something like:

classStartDate >= reportdate and
classEndDate <= reportdate

I would truncate all the times for the where clause so they wouldn't affect the selection.

Relatively straight forward.. just not on the Crystal side.

Lisa