Events within 24 hours of each other 1

[JC442]

Report Purpose: show speakers with 3 or more events in a 24 hour period.
Data: Speaker_ID, Event_ID, Event_DATETIME.
It is very easy to Group the report by Speaker_ID and
Event_DATETIME (day) and supress Event_DATETIME Groups with less than 3 Events.

The problem is how to do the same thing, but use 24 hours, which can span across different days. For example a speaker could have one event at 2:00pm 7 March, one at 6:00 pm 7 March and one at 9:00 am 8 March. Ideally the report should show the speaker and the 3 events and dates together and only once on the report.

 

[MisterMo]

Hi could you give a sample of the data also which version of Crystal and Database


-Mo

 

[Madawc]

I've a feeling you're asking Crystal to do more than it is capable of. For a group you can compair minimum and maximum.

Or you can compair a record to NEXT and PREVIOUS, possibly this would do it, a record which is neither the first nor last and where the difference (DATEDIFF("h" etc.) adds up to 24 or less for the two cases. But this is way outside anything I have tried. You'll need to experiment.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

What would you expect to see if the speaker had events like this:

3/7/06
8 am
10 am
9 pm

3/8/06
7 am
9 am
10 am
12 pm
7 pm

3/9
7 am
9 am

What display would you want to see in this case?

-LB

 

[JC442]

LB,
Excellent question.
The data should be grouped first by speaker and then by 24 hour period beginning with the first event date/time and moving forward 24 hours, then beginning again and taking the events in the next 24 hours. To use your outstanding example:
3/7/06 8am
3/7/06 10am
3/7/06 9pm
3/7/06 8am
3/8/06 7am

3/8/06 9am
3/8/06 10am
3/8/06 12pm
3/8/06 7pm
3/9/06 7am

3/9/06 9am

If it is not possible to get to a 24 hour period starting at the first chronological event, and counting out 24 hours,
it would be ok to get all events in a list that are within 24 hours of each other with the same speaker. The thing is that there are thousands of events and I want to suppress those where there are less than 3 events for the same speaker within a 24 hour period.

Thanks for your response.

JC

 

[JC442]

LB,
Thanks Again for asking the right question. The solution here was to group the events by speaker and startdate/time in the main report.

Then supress events with start dates within 24 hours of the previous event startdate/time in the main report.

Then insert a subreport at the startdate/time level in the main report. The sub report pulls events with startdate/times in the next 24 hours using the speaker and event startdate parameters.

Nothing helps getting what you want more than knowing what you want...