counting one appointment for client with multiple appts in a day

[TerriO]

I have a facility as a group
Then I show all the hours for the day
I need to count the appointments for each hour for the day, but I only want to count a client once for the day that may have multiple appointments that day
Example: I have 2 clients for the time range below
Client A has an appointment at 2 pm and 4 pm
Client B has an Appointment at 4 pm (there will be multiple clients for each hour)

The results should look like:

12 pm
1 pm
2 pm 1
3 pm
4 pm 1
Total 2

Next Facilty Group, etc.

What is the best way to do this, I have fuddled with it, know there is an easy solution but recollection doesnt serve me well today. (p.s. I need to show the hours even if there is no count for that hour, the only way I can think of now is to write 24 formulas for if then else in that time bucket, unless there is a more modern suggestion?

Terri

 

[synapsevampire]

Create another group by the hour (insert the datetime field and slect for each hour), and use a distinct count of clients for the hour.

Crystal does not fabricate data, so you won't get hours for those times that don't exist in the database.

To accomplish this, consider either creating all of the hours as individual Running Totals with distinct count of clients for each, or set up another table in the database which has all of the hours in the table and do a left outer from the hours table to the table containing your data.

-k

 

[TerriO]

I understand the distinct count by the hour but I need a distinct count for the client for the day and 1 client a day regardless of how many appointments that client has and it needs to be counted in 1 appt time only for the whole day.

Terri

 

[Madawc]

I can think of two methods:
a) Group by day, in addition to your existing groupings.
b) Create a formula field in which the appointment is turned into a date; Date(Appointment) ought to do it. Then do a distinct count on this date-only field.

It helps to give your Crystal version, since newer versions have extra solutions, and some extra problems. I use Crystal 8.5

Madawc Williams (East Anglia)