Selecting/Grouping by the First Record Only 1

[bill1345]

Table: Times
CaseNum (Number)
AnaestheticType (String)
StartTime (Time)

Sample Data:
CaseNum AnaestheticType StartTime
1 GA 08:30
1 LA 09:30
2 GA 10:00

I want to group by the Time Period depending on the first StartTime for the case. The 3 periods are:
00:00 to 09:00
09:00 to 18:00
18:00 to 00:00

The result I get is:
Group 00:00 to 09:00
CaseNum AnaestheticType StartTime
1 GA 08:30
Group 09:00 to 18:00
CaseNum AnaestheticType StartTime
1 LA 09:30
2 GA 10:00

Case 1 should not appear in the 09:00 to 18:00 group as the case started in the 00:00 to 09:00 period.

The result I want is:
Group 00:00 to 09:00
CaseNum AnaestheticType StartTime
1 GA 08:30
Group 09:00 to 18:00
CaseNum AnaestheticType StartTime
2 GA 10:00

The Group uses a SPECIFIED ORDER with {Times.StartTime} IS BETWEEN <time> and <time>
I have tried suppressing records with {Times.CaseNum} = Previous{Times.CaseNum} which works but only if the two times for the case are in the same period.

I can’t figure out how to select only the record with the earliest time.

I use Crystal Reports 7 and the database is Btrieve tables.

Any suggestions will be helpful.

 

[synapsevampire]

If you're grouping via a specified order, then you cannot overide it with a formula, it's one or the other.

Create a Group By report which returns the minimum(starttime).

Insert a group by starttime, and do NOT use specified order, use ascending.

Now use the Report->Edit Selection Formula->Group and place something like:

{table.starttime} = minimum({table.starttime})

Now you have only the data of interest.

I would use a View rather than Crystal to do this, but this should work fine as well.

-k

 

[bill1345]

Thanks for the reply. I can get a group footer to produce the first start time for each case which is what I want.

I want to group the cases into 1 of the three time periods:
00:00 to 09:00
09:00 to 18:00
18:00 to 00:00

Is there a way I can report the number of cases in each group (depending on the 1st time for each case) to get:

Group 00:00 to 09:00
CaseNum AnaestheticType StartTime
1 GA 08:30
Group 09:00 to 18:00
CaseNum AnaestheticType StartTime
2 GA 10:00

 

[lbass]

You can't add a group for time ranges without causing multiple cases to reappear, even if you are using the group footer, so this is a little trickier than it appears. The following shows you how to create fake groups to arrive at your desired display.

Group on {table.caseno} and then go to report->edit selection formula->GROUP and enter:

{table.time} = minimum({table.time},{table.caseno})

This will return only the first time for each case number. Now create three detail sections, and place your detail fields in detail_b: {table.CaseNo} {table.AnesthType} {table.Time}

Next go to report->topN and choose &quot;all&quot; and select &quot;minimum of {table.time}, descending. This will put all cases in ascending order of the time field (but descending order of the minimum!).

Then in detail_a, place a text box for your heading, such as: &quot;00:00 to 09:00&quot;. Repeat for your other two headings.
You now have three headings repeating for each detail. Next create a running total for each time period, using the running total editor. For {#cnt0009}, select {table.time}, count, evaluate based on a formula:

{table.time} in time(00,00,00) to time(09,00,00)

Reset never. Create running totals for the other two time periods, just changing the time in the formula, making sure you don't have overlap, e.g., using time(23,59,59) for your last end period.

Place the running totals in detail_c. You now have three running totals showing after each detail.

Next go to your heading text fields, and one by one, select them and go to format field->common->suppress->x+2 and enter:

{#cnt0009} <> 1 //for the 00:00 to 09:00 header

Repeat for the other two headers, using the corresponding running total. Now reposition the headers so they are on top of one another.

Next select each running totals field, one at a time, and go to format field->common->suppress->x+2 and enter:

Not({table.time} in time(00,00,00) to time(09,00,00) or
next({table.time}) in time(00,00,00) to time(09,00,00)

Repeat using the appropriate time periods for each running total. Then place each field on top of each other, so they are in the same position.

Finally, go to format section and for each detail section, check &quot;suppress blank section.&quot;

This should give you a format like the following:

00:00 to 09:00
3 GA 08:30
4 AB 08:45
1 GA 08:50
3 //count

09:01 to 18:00
2 CD 09:15
6 AB 09:20
2 //count

That should do it.

-LB

 

[bill1345]

Thanks LBASS.

Very good answer. I would not of thought of doing it this way.

Regards.

Bill