Formula Help - Find first appt then stop counting 1

[jenschmidt]

I've wracked my brain and can't figure out how to solve this - I'm sure you guys will know right off the top of your heads!

Using v9.

Report set up:
GH1 Department
GH2 Doctor
GH3 Date of Appt (daily)
Details {Appt Time} {Appt Length}

For an easy example:
Sun Prairie
Dr. Schmidt
6/7/04
9am 10min
10am 10min
11am 10min
6/8/04
1pm 10min
2pm 10min
3pm 10min
6/9/04
9am 10min
10am 10min
1pm 10min
2pm 10min

The report needs to be broken down by AM and PM sessions. An AM session is 300 minutes from 7am to 11:59am, PM session is 300 minutes from 12pm to 5pm. If a doctor has even just one appointment between the hours of 7 to 12, they are considered to have worked an AM session of 300 minutes. Same applies for PM sessions.

So this is what I need the output to look like:
AM Ct AM Min PM Ct PM Min
Sun Prairie 2 300 2 300
Dr Schmidt 2 300 2 300
6/7/04 1 300 0 0
6/8/04 0 0 1 300
6/9/04 1 300 1 300

For the sessions, I want it to find the first qualifying appt, return a result of 300 minutes and then stop counting, even though there are other appts that would qualify.

I'm somewhat blinded by the details at this point so please let me know if I haven't explained accurately enough and I'll try it again. Otherwise, any thoughts or ideas would be greatly appreciated!

Thank you!

jennifer.giemza@uwmf.wisc.edu

 

[Katy44]

You could always do a count of the minutes, and then set up a formula to say if it's zero, then zero, else 300.

 

[jenschmidt]

If I do that at a detail level, I can't summarize it because, for instance, I would get 900 minutes for 6/7. I can get it to work at the "date of appt" level, but not at the other levels. Thanks for the thoughts - wish it were that easy!

jennifer.giemza@uwmf.wisc.edu

 

[Katy44]

Can you not create the formula based on the summary?

 

[jenschmidt]

I can, but I don't know how to get it to look at each "date of appt" group and only count 1 record even though there's 3 records that qualify and then end up with a count of 2 at the doctor level.

jennifer.giemza@uwmf.wisc.edu

 

[kmcginn]

Crystal has a function called OnFirstRecord. Maybe you caould use that in a formula.
If OnFirstRecord then true else false

Then, suppress the record or group or section (depending on your setup) based on the formula.

That should give you only one record per group, if you group on the doctor or date.

Hope that helped some.

 

[jenschmidt]

Thanks for the input! It doesn't look like OnFirstRecord will work because for 6/9, the first record is an AM session, so I'm not sure how I would get to the PM sessions.

jennifer.giemza@uwmf.wisc.edu

 

[lbass]

I think you could create formulas like:

{@AMcount}:
if minimum({table.appttime},{table.date}) in time(07,00,00) to time(11,59,59) then 1

For {@AMminutes} change the 1 to 300.

{@PMcount}:
if maximum({table.appttime},{table.date}) in time(12,00,00) to time(17,00,00) then 1 //change the 1 to 300 for {@PMminutes}

Then to summarize these, you will need to use variables, using the three formula method:

//{@resetcity} to be placed in the group (city) header:
whileprintingrecords;
numbervar citytotAMmins := 0:

//{@resetdoc} to be placed in the group (doctor) header:
whileprintingrecords;
numbervar doctotAMmins := 0;

//{@accum} to be placed in the date group header or footer:
whileprintingrecords;
numbervar doctotAMmins := doctotAMmins + {@AMminutes};
numbervar citytotAMmins := citytotAMmins + {@AMminutes};

//{@displaydoc} to be placed in the group footer (doctor):
whileprintingrecords;
numbervar doctotAMmins;

//{@displaycity} to be placed in the group footer (city):
whileprintingrecords;
numbervar citytotAMmins;

I assumed you meant to add the 300 mins from different days.

-LB

 

[jenschmidt]

Thanks so much for the help lbass!! It works beautifully!

jennifer.giemza@uwmf.wisc.edu

 

[jenschmidt]

HEEEELLLLLLPPP LBASS!! (or anyone else that might be able to see what I'm doing wrong!)

It seems that the AM formulas are working perfectly but the PM formulas for minutes are not. I've checked all the formulas over and over and over again to see if there are any differences other than what time of day to be looking at and I can't see one thing that's different. I made sure and double sure that I have all the variables in the correct sections according to the directions lbass gave. I've tried to split up the report into two - one AM only and one PM only, but I still get the same results.

In the final output, I'm only showing the end user the City and Doctor levels. Things have been moved to the group footers so it's a bit upside down from my original example. Here's an example of what I'm seeing in the PM report:

DOCTOR A 2 0
DOCTOR B 4 300
DOCTOR C 4 300
DOCTOR D 3 300
DOCTOR E 4 300
DOCTOR F 5 0
DOCTOR G 5 0
DOCTOR H 3 600

SUN PRAIRIE 30 1,800

At this level, it seems to be calculating properly. But, when I drill down to check the numbers, here's an example of what I see at the group 3 level:

06/10/04 1 300
06/11/04 1 300
DOCTOR A 2 0

06/07/04 1 300
06/08/04 1 300
06/09/04 0 0
06/10/04 1 300
06/11/04 1 300
DOCTOR E 4 300

06/07/04 1 300
06/08/04 1 300
06/09/04 0 0
06/10/04 1 300
06/11/04 0 0
06/12/04 0 0
06/13/04 0 0
DOCTOR H 3 600

The counts for each doctor are correct, but the minutes are wrong - Dr A should be 600, Dr E should be 1200, Dr H should be 900.

For Dr H, I know for sure 6/9 is actually returning null values. Initially, this was giving me problems with the final counts, but I "fixed" it (maybe I broke it) by doing the following:
________________________________________________
@PM Assumed Minutes Prov/Dept
if isnull({@Time}) then 0 else
if minimum({@Time},{PAT_ENC.CONTACT_DATE},"daily") in
Time(12,00,00) to Time(17,00,00) then 300 else 0

This is lbass's {@AMminutes} formula. The first line is what I added to take care of nulls and {@Time} is TimeValue({PAT_ENC.APPT_TIME}).
________________________________________________
@PM Session Ct Prov/Dept
if {@PM Session Ct Day}>0 then 1

This is lbass's {@AMcount} formula. {@PM Session Ct Day} is
if isnull({PAT_ENC.APPT_TIME}) then 0 else
if right(totext(TimeValue({PAT_ENC.APPT_TIME})),2)="pm"
then 1 else 0
________________________________________________
@Accumulate Min/Ct
WhilePrintingRecords;
NumberVar ProvTotPMmins :=
ProvTotPMmins + {@PM Assumed Min Prov/Dept};
NumberVar DeptTotPMmins :=
DeptTotPMmins + {@PM Assumed Min Prov/Dept};
NumberVar ProvTotPMct :=
ProvTotPMct + {@PM Session Ct Prov/Dept};
NumberVar DeptTotPMct :=
DeptTotPMct + {@PM Session Ct Prov/Dept};

This is lbass's {@accum} formula. DeptTotAMmins is the variable he set up as citytotAMmins. ProvTotAMmins is the variable he set up as doctoAMmins.
________________________________________________
Again, all the other variables are there (@reset and @display) and in the correct sections.

Anything else you can see that I'm goofing up?

Thanks again for the help! It's much appreciated!









jennifer.giemza@uwmf.wisc.edu

 

[lbass]

I'm not following all that you did, but one of your formulas is wrong. It should be:

//@PM Assumed Minutes Prov/Dept:
if isnull({@Time}) then 0 else
if maximum({@Time},{PAT_ENC.CONTACT_DATE},"daily") in
Time(12,00,00) to Time(17,00,00) then 300 else 0

Let me know if that makes the difference.

-LB

 

[jenschmidt]

Duh!! Sorry about that!! It was right in front of me the whole time and I looked right past it!!

Thank you!! Thank you!! For sharing your amazing talent at Crystal with the rest of us!!

jennifer.giemza@uwmf.wisc.edu

 

[jenschmidt]

I'm sorry to keep asking for help on this one - I hope you wouldn't mind contributing a few more minutes of time lbass. It seems like just when I think it's working, I spot something that isn't.

I have the report split into two reports - one for AM and one for PM - because if I have both sets of formulas on the same report, one or the other doesn't work. I have a formula set up that finds the AM Times and the PM Times. When I sort in descending by AM Times, AM works but not PM and vice versa. We'll have to live with this for now I think.

The issue currently is the PM Minutes are still not calculating correctly. I changed the formula lbass noted as incorrect in an earlier post (thanks again lbass!!) and initially I thought it worked.

Here's what I'm seeing:

DOCTOR A 2 600
DOCTOR B 4 900
DOCTOR C 4 900
DOCTOR D 3 600
DOCTOR E 4 900
DOCTOR F 5 1,500
DOCTOR G 5 1,500
DOCTOR H 3 900
SUN PRAIRIE TOT 30 7,800

For every session counted in the first column, there should be 300 minutes reflected in the second column. All the counts are correct. For minutes, Dr A is correct, but Dr B should show 1200 minutes. I thought it might have been related to null values coming into the report at the detail level, but doctors calculating correctly AND doctors calculating incorrectly are both displaying nulls at times.

Any other thoughts or directions I should be looking into?


jennifer.giemza@uwmf.wisc.edu

 

[lbass]

Is there a pattern of error? Are there errors with any other doctors' results? Please provide the detail level results for several doctors, including doctors before and after doctors that have errors.

-LB

 

[jenschmidt]

Thanks again for the help lbass! I really appreciate it!

It doesn't seem to me that there is a pattern of error - the first dr is correct, the middle 4 are incorrect and the remaining are correct.

The following is the detail. If the APPT_TIME field is blank, that's what I mean when I say "pulling in nulls". (I’m sorry about the length of this detail – I was hoping to give enough info to see some kind of pattern or error. In this data, Dr's A and F are correct, Dr's B-E are incorrect.)

Prov ID Contact Date APPT_TIME
DOCTOR A 06/10/04 4:15 PM
DOCTOR A 06/10/04 3:45 PM
DOCTOR A 06/10/04 3:30 PM
DOCTOR A 06/10/04 3:15 PM
DOCTOR A 06/10/04 3:00 PM
DOCTOR A 06/10/04 2:45 PM
DOCTOR A 06/10/04 2:30 PM
DOCTOR A 06/10/04 2:15 PM
DOCTOR A 06/10/04 2:00 PM
DOCTOR A 06/10/04 1:15 PM
DOCTOR A 06/10/04 1:00 PM
DOCTOR A 06/10/04 11:30 AM
DOCTOR A 06/10/04 11:15 AM
DOCTOR A 06/10/04 11:15 AM
DOCTOR A 06/10/04 10:30 AM
DOCTOR A 06/10/04 9:30 AM
DOCTOR A 06/11/04 4:30 PM
DOCTOR A 06/11/04 4:00 PM
DOCTOR A 06/11/04 3:45 PM
DOCTOR A 06/11/04 3:30 PM
DOCTOR A 06/11/04 3:15 PM
DOCTOR A 06/11/04 3:00 PM
DOCTOR A 06/11/04 2:45 PM
DOCTOR A 06/11/04 2:30 PM
DOCTOR A 06/11/04 2:15 PM
DOCTOR A 06/11/04 2:00 PM
DOCTOR A 06/11/04 1:45 PM
DOCTOR A 06/11/04 1:00 PM
DOCTOR A 06/11/04 11:30 AM
DOCTOR A 06/11/04 11:15 AM
DOCTOR A 06/11/04 11:00 AM
DOCTOR A 06/11/04 10:30 AM
DOCTOR A 06/11/04 9:30 AM
DOCTOR A TOTAL 2 600


DOCTOR B 06/06/04
DOCTOR B 06/07/04 1:45 PM
DOCTOR B 06/07/04 1:00 PM
DOCTOR B 06/07/04 12:45 PM
DOCTOR B 06/07/04 12:45 PM
DOCTOR B 06/07/04 12:30 PM
DOCTOR B 06/07/04 12:30 PM
DOCTOR B 06/07/04 12:00 PM
DOCTOR B 06/07/04 10:15 AM
DOCTOR B 06/07/04 10:00 AM
DOCTOR B 06/07/04 9:30 AM
DOCTOR B 06/07/04 9:15 AM
DOCTOR B 06/07/04 9:00 AM
DOCTOR B 06/07/04 8:45 AM
DOCTOR B 06/07/04 8:15 AM
DOCTOR B 06/07/04 7:30 AM
DOCTOR B 06/07/04
DOCTOR B 06/08/04 10:30 AM
DOCTOR B 06/08/04 10:15 AM
DOCTOR B 06/08/04 10:00 AM
DOCTOR B 06/08/04 9:45 AM
DOCTOR B 06/08/04 9:45 AM
DOCTOR B 06/08/04 9:30 AM
DOCTOR B 06/08/04 9:15 AM
DOCTOR B 06/08/04 9:15 AM
DOCTOR B 06/08/04 9:00 AM
DOCTOR B 06/09/04 3:30 PM
DOCTOR B 06/09/04 3:15 PM
DOCTOR B 06/09/04 3:00 PM
DOCTOR B 06/09/04 2:30 PM
DOCTOR B 06/09/04 2:00 PM
DOCTOR B 06/09/04 1:00 PM
DOCTOR B 06/09/04 11:00 AM
DOCTOR B 06/09/04 10:45 AM
DOCTOR B 06/09/04 10:15 AM
DOCTOR B 06/09/04 9:45 AM
DOCTOR B 06/09/04 9:30 AM
DOCTOR B 06/09/04 9:15 AM
DOCTOR B 06/09/04 8:45 AM
DOCTOR B 06/09/04 8:30 AM
DOCTOR B 06/09/04
DOCTOR B 06/09/04
DOCTOR B 06/09/04
DOCTOR B 06/09/04
DOCTOR B 06/10/04 7:30 PM
DOCTOR B 06/10/04 7:00 PM
DOCTOR B 06/10/04 6:45 PM
DOCTOR B 06/10/04 6:30 PM
DOCTOR B 06/10/04 6:15 PM
DOCTOR B 06/10/04 5:45 PM
DOCTOR B 06/10/04 5:30 PM
DOCTOR B 06/10/04 5:30 PM
DOCTOR B 06/10/04 5:30 PM
DOCTOR B 06/10/04 4:15 PM
DOCTOR B 06/10/04 4:00 PM
DOCTOR B 06/10/04 3:30 PM
DOCTOR B 06/10/04 3:15 PM
DOCTOR B 06/10/04 3:00 PM
DOCTOR B 06/10/04 2:45 PM
DOCTOR B 06/10/04 2:30 PM
DOCTOR B 06/10/04 2:15 PM
DOCTOR B 06/10/04 2:00 PM
DOCTOR B 06/10/04 1:30 PM
DOCTOR B 06/10/04 1:30 PM
DOCTOR B 06/10/04 1:00 PM
DOCTOR B 06/10/04
DOCTOR B 06/10/04
DOCTOR B 06/11/04 3:30 PM
DOCTOR B 06/11/04 3:00 PM
DOCTOR B 06/11/04 2:45 PM
DOCTOR B 06/11/04 2:30 PM
DOCTOR B 06/11/04 2:15 PM
DOCTOR B 06/11/04 2:00 PM
DOCTOR B 06/11/04 1:45 PM
DOCTOR B 06/11/04 1:15 PM
DOCTOR B 06/11/04 1:00 PM
DOCTOR B 06/11/04 11:15 AM
DOCTOR B 06/11/04 11:00 AM
DOCTOR B 06/11/04 10:45 AM
DOCTOR B 06/11/04 10:15 AM
DOCTOR B 06/11/04 10:00 AM
DOCTOR B 06/11/04 9:45 AM
DOCTOR B 06/11/04 9:30 AM
DOCTOR B 06/11/04 9:00 AM
DOCTOR B 06/11/04
DOCTOR B TOTAL 4 900


DOCTOR C 06/07/04 2:00 PM
DOCTOR C 06/07/04 1:40 PM
DOCTOR C 06/07/04 1:20 PM
DOCTOR C 06/07/04 1:10 PM
DOCTOR C 06/07/04 1:00 PM
DOCTOR C 06/07/04 1:00 PM
DOCTOR C 06/07/04 12:30 PM
DOCTOR C 06/07/04 12:00 PM
DOCTOR C 06/07/04 10:10 AM
DOCTOR C 06/07/04 10:00 AM
DOCTOR C 06/07/04 9:50 AM
DOCTOR C 06/07/04 9:40 AM
DOCTOR C 06/07/04 9:30 AM
DOCTOR C 06/07/04 9:00 AM
DOCTOR C 06/07/04 8:50 AM
DOCTOR C 06/07/04 8:40 AM
DOCTOR C 06/07/04 8:30 AM
DOCTOR C 06/07/04 8:30 AM
DOCTOR C 06/07/04 8:20 AM
DOCTOR C 06/07/04 8:10 AM
DOCTOR C 06/07/04 8:10 AM
DOCTOR C 06/07/04 7:40 AM
DOCTOR C 06/07/04 7:40 AM
DOCTOR C 06/07/04 7:30 AM
DOCTOR C 06/07/04
DOCTOR C 06/08/04 4:30 PM
DOCTOR C 06/08/04 4:20 PM
DOCTOR C 06/08/04 4:10 PM
DOCTOR C 06/08/04 4:00 PM
DOCTOR C 06/08/04 3:50 PM
DOCTOR C 06/08/04 3:40 PM
DOCTOR C 06/08/04 3:30 PM
DOCTOR C 06/08/04 3:00 PM
DOCTOR C 06/08/04 2:50 PM
DOCTOR C 06/08/04 2:30 PM
DOCTOR C 06/08/04 2:20 PM
DOCTOR C 06/08/04 2:00 PM
DOCTOR C 06/08/04 1:20 PM
DOCTOR C 06/08/04 1:00 PM
DOCTOR C 06/08/04 11:20 AM
DOCTOR C 06/08/04 11:10 AM
DOCTOR C 06/08/04 11:10 AM
DOCTOR C 06/08/04 11:00 AM
DOCTOR C 06/08/04 10:30 AM
DOCTOR C 06/08/04 10:20 AM
DOCTOR C 06/08/04 10:00 AM
DOCTOR C 06/08/04 9:50 AM
DOCTOR C 06/08/04 9:40 AM
DOCTOR C 06/08/04 9:40 AM
DOCTOR C 06/08/04 9:30 AM
DOCTOR C 06/08/04 9:00 AM
DOCTOR C 06/08/04
DOCTOR C 06/08/04
DOCTOR C 06/09/04 7:40 PM
DOCTOR C 06/09/04 7:30 PM
DOCTOR C 06/09/04 7:00 PM
DOCTOR C 06/09/04 6:20 PM
DOCTOR C 06/09/04 6:10 PM
DOCTOR C 06/09/04 6:00 PM
DOCTOR C 06/09/04 5:50 PM
DOCTOR C 06/09/04 5:40 PM
DOCTOR C 06/09/04 5:30 PM
DOCTOR C 06/09/04 4:30 PM
DOCTOR C 06/09/04 4:00 PM
DOCTOR C 06/09/04 3:50 PM
DOCTOR C 06/09/04 3:30 PM
DOCTOR C 06/09/04 3:20 PM
DOCTOR C 06/09/04 3:10 PM
DOCTOR C 06/09/04 3:00 PM
DOCTOR C 06/09/04 2:50 PM
DOCTOR C 06/09/04 2:40 PM
DOCTOR C 06/09/04 2:10 PM
DOCTOR C 06/09/04 2:00 PM
DOCTOR C 06/09/04 2:00 PM
DOCTOR C 06/09/04 1:40 PM
DOCTOR C 06/09/04 1:10 PM
DOCTOR C 06/09/04 1:00 PM
DOCTOR C 06/09/04 1:00 PM
DOCTOR C 06/09/04 1:00 PM
DOCTOR C 06/09/04
DOCTOR C 06/10/04 11:30 AM
DOCTOR C 06/10/04 11:20 AM
DOCTOR C 06/10/04 11:10 AM
DOCTOR C 06/10/04 9:50 AM
DOCTOR C 06/10/04 9:00 AM
DOCTOR C 06/10/04 9:00 AM
DOCTOR C 06/10/04 8:30 AM
DOCTOR C 06/10/04 7:30 AM
DOCTOR C 06/10/04
DOCTOR C 06/11/04 4:30 PM
DOCTOR C 06/11/04 3:40 PM
DOCTOR C 06/11/04 3:30 PM
DOCTOR C 06/11/04 3:20 PM
DOCTOR C 06/11/04 3:10 PM
DOCTOR C 06/11/04 3:00 PM
DOCTOR C 06/11/04 2:40 PM
DOCTOR C 06/11/04 2:30 PM
DOCTOR C 06/11/04 2:30 PM
DOCTOR C 06/11/04 2:20 PM
DOCTOR C 06/11/04 2:20 PM
DOCTOR C 06/11/04 2:00 PM
DOCTOR C 06/11/04 1:30 PM
DOCTOR C 06/11/04 1:10 PM
DOCTOR C 06/11/04 1:00 PM
DOCTOR C 06/11/04 11:30 AM
DOCTOR C 06/11/04 11:20 AM
DOCTOR C 06/11/04 11:10 AM
DOCTOR C 06/11/04 11:00 AM
DOCTOR C 06/11/04 10:50 AM
DOCTOR C 06/11/04 10:20 AM
DOCTOR C 06/11/04 10:10 AM
DOCTOR C 06/11/04 10:00 AM
DOCTOR C 06/11/04 9:50 AM
DOCTOR C 06/11/04 9:40 AM
DOCTOR C 06/11/04 9:30 AM
DOCTOR C 06/11/04 9:20 AM
DOCTOR C 06/11/04 9:10 AM
DOCTOR C 06/11/04 9:10 AM
DOCTOR C 06/11/04 9:00 AM
DOCTOR C 06/11/04
DOCTOR C 06/12/04
DOCTOR C 06/13/04
DOCTOR C 06/13/04
DOCTOR C 06/13/04
DOCTOR C TOTAL 4 900


DOCTOR D 06/06/04
DOCTOR D 06/07/04 4:15 PM
DOCTOR D 06/07/04 4:00 PM
DOCTOR D 06/07/04 4:00 PM
DOCTOR D 06/07/04 3:30 PM
DOCTOR D 06/07/04 3:15 PM
DOCTOR D 06/07/04 3:00 PM
DOCTOR D 06/07/04 2:45 PM
DOCTOR D 06/07/04 2:30 PM
DOCTOR D 06/07/04 2:15 PM
DOCTOR D 06/07/04 2:00 PM
DOCTOR D 06/07/04 1:30 PM
DOCTOR D 06/07/04 12:45 PM
DOCTOR D 06/07/04 11:30 AM
DOCTOR D 06/07/04 11:00 AM
DOCTOR D 06/07/04 10:45 AM
DOCTOR D 06/07/04 10:30 AM
DOCTOR D 06/07/04 10:30 AM
DOCTOR D 06/07/04 10:00 AM
DOCTOR D 06/07/04 9:30 AM
DOCTOR D 06/07/04
DOCTOR D 06/08/04 7:45 PM
DOCTOR D 06/08/04 7:30 PM
DOCTOR D 06/08/04 7:15 PM
DOCTOR D 06/08/04 7:00 PM
DOCTOR D 06/08/04 7:00 PM
DOCTOR D 06/08/04 6:45 PM
DOCTOR D 06/08/04 6:30 PM
DOCTOR D 06/08/04 6:30 PM
DOCTOR D 06/08/04 6:15 PM
DOCTOR D 06/08/04 6:00 PM
DOCTOR D 06/08/04 5:30 PM
DOCTOR D 06/08/04 4:30 PM
DOCTOR D 06/08/04 4:00 PM
DOCTOR D 06/08/04 3:45 PM
DOCTOR D 06/08/04 3:30 PM
DOCTOR D 06/08/04 3:00 PM
DOCTOR D 06/08/04 2:45 PM
DOCTOR D 06/08/04 2:30 PM
DOCTOR D 06/08/04 2:30 PM
DOCTOR D 06/08/04 2:15 PM
DOCTOR D 06/08/04 2:00 PM
DOCTOR D 06/08/04 1:45 PM
DOCTOR D 06/08/04 1:30 PM
DOCTOR D 06/08/04 1:30 PM
DOCTOR D 06/08/04 1:00 PM
DOCTOR D 06/09/04 4:30 PM
DOCTOR D 06/09/04 4:15 PM
DOCTOR D 06/09/04 4:00 PM
DOCTOR D 06/09/04 4:00 PM
DOCTOR D 06/09/04 3:45 PM
DOCTOR D 06/09/04 3:15 PM
DOCTOR D 06/09/04 2:45 PM
DOCTOR D 06/09/04 2:30 PM
DOCTOR D 06/09/04 2:15 PM
DOCTOR D 06/09/04 2:00 PM
DOCTOR D 06/09/04 1:45 PM
DOCTOR D 06/09/04 1:30 PM
DOCTOR D 06/09/04 1:30 PM
DOCTOR D 06/09/04 1:00 PM
DOCTOR D 06/09/04 11:30 AM
DOCTOR D 06/09/04 11:00 AM
DOCTOR D 06/09/04 10:30 AM
DOCTOR D 06/09/04 10:00 AM
DOCTOR D 06/09/04 9:30 AM
DOCTOR D 06/09/04
DOCTOR D 06/09/04
DOCTOR D 3 600


DOCTOR E 06/07/04 7:40 PM
DOCTOR E 06/07/04 6:10 PM
DOCTOR E 06/07/04 6:00 PM
DOCTOR E 06/07/04 5:50 PM
DOCTOR E 06/07/04 5:50 PM
DOCTOR E 06/07/04 5:30 PM
DOCTOR E 06/07/04 4:30 PM
DOCTOR E 06/07/04 4:10 PM
DOCTOR E 06/07/04 4:00 PM
DOCTOR E 06/07/04 4:00 PM
DOCTOR E 06/07/04 3:50 PM
DOCTOR E 06/07/04 3:10 PM
DOCTOR E 06/07/04 2:20 PM
DOCTOR E 06/07/04 2:10 PM
DOCTOR E 06/07/04 2:00 PM
DOCTOR E 06/07/04 1:50 PM
DOCTOR E 06/07/04 1:40 PM
DOCTOR E 06/07/04 1:30 PM
DOCTOR E 06/07/04 1:10 PM
DOCTOR E 06/07/04 1:00 PM
DOCTOR E 06/07/04
DOCTOR E 06/07/04
DOCTOR E 06/08/04 2:00 PM
DOCTOR E 06/08/04 1:50 PM
DOCTOR E 06/08/04 1:40 PM
DOCTOR E 06/08/04 1:30 PM
DOCTOR E 06/08/04 1:20 PM
DOCTOR E 06/08/04 1:10 PM
DOCTOR E 06/08/04 12:50 PM
DOCTOR E 06/08/04 12:40 PM
DOCTOR E 06/08/04 12:20 PM
DOCTOR E 06/08/04 12:10 PM
DOCTOR E 06/08/04 12:00 PM
DOCTOR E 06/08/04 10:30 AM
DOCTOR E 06/08/04 9:50 AM
DOCTOR E 06/08/04 9:40 AM
DOCTOR E 06/08/04 9:30 AM
DOCTOR E 06/08/04 9:20 AM
DOCTOR E 06/08/04 9:00 AM
DOCTOR E 06/08/04 8:40 AM
DOCTOR E 06/08/04 8:30 AM
DOCTOR E 06/08/04 8:20 AM
DOCTOR E 06/08/04 8:10 AM
DOCTOR E 06/08/04 7:30 AM
DOCTOR E 06/08/04
DOCTOR E 06/08/04
DOCTOR E 06/08/04
DOCTOR E 06/09/04 10:10 AM
DOCTOR E 06/09/04 10:00 AM
DOCTOR E 06/09/04 9:50 AM
DOCTOR E 06/09/04 9:20 AM
DOCTOR E 06/09/04 8:20 AM
DOCTOR E 06/09/04 8:10 AM
DOCTOR E 06/09/04 7:40 AM
DOCTOR E 06/09/04 7:30 AM
DOCTOR E 06/09/04
DOCTOR E 06/09/04
DOCTOR E 06/09/04
DOCTOR E 06/10/04 4:30 PM
DOCTOR E 06/10/04 4:20 PM
DOCTOR E 06/10/04 4:10 PM
DOCTOR E 06/10/04 4:00 PM
DOCTOR E 06/10/04 3:50 PM
DOCTOR E 06/10/04 3:30 PM
DOCTOR E 06/10/04 3:20 PM
DOCTOR E 06/10/04 3:00 PM
DOCTOR E 06/10/04 2:40 PM
DOCTOR E 06/10/04 2:20 PM
DOCTOR E 06/10/04 2:00 PM
DOCTOR E 06/10/04 1:50 PM
DOCTOR E 06/10/04 1:30 PM
DOCTOR E 06/10/04 1:20 PM
DOCTOR E 06/10/04 1:00 PM
DOCTOR E 06/10/04 1:00 PM
DOCTOR E 06/10/04 11:30 AM
DOCTOR E 06/10/04 11:20 AM
DOCTOR E 06/10/04 11:20 AM
DOCTOR E 06/10/04 11:10 AM
DOCTOR E 06/10/04 10:50 AM
DOCTOR E 06/10/04 10:30 AM
DOCTOR E 06/10/04 9:40 AM
DOCTOR E 06/10/04 9:00 AM
DOCTOR E 06/10/04
DOCTOR E 06/11/04 4:30 PM
DOCTOR E 06/11/04 4:10 PM
DOCTOR E 06/11/04 4:00 PM
DOCTOR E 06/11/04 4:00 PM
DOCTOR E 06/11/04 3:40 PM
DOCTOR E 06/11/04 3:30 PM
DOCTOR E 06/11/04 3:20 PM
DOCTOR E 06/11/04 3:00 PM
DOCTOR E 06/11/04 2:50 PM
DOCTOR E 06/11/04 2:40 PM
DOCTOR E 06/11/04 2:30 PM
DOCTOR E 06/11/04 1:30 PM
DOCTOR E 06/11/04 1:10 PM
DOCTOR E 06/11/04 1:00 PM
DOCTOR E 06/11/04 11:30 AM
DOCTOR E 06/11/04 11:20 AM
DOCTOR E 06/11/04 11:10 AM
DOCTOR E 06/11/04 11:00 AM
DOCTOR E 06/11/04 10:50 AM
DOCTOR E 06/11/04 10:50 AM
DOCTOR E 06/11/04 10:40 AM
DOCTOR E 06/11/04 10:30 AM
DOCTOR E 06/11/04 10:20 AM
DOCTOR E 06/11/04 10:10 AM
DOCTOR E 06/11/04 10:00 AM
DOCTOR E 06/11/04 10:00 AM
DOCTOR E 06/11/04 9:50 AM
DOCTOR E 06/11/04 9:40 AM
DOCTOR E 06/11/04 9:00 AM
DOCTOR E 06/11/04
DOCTOR E 06/11/04
DOCTOR E TOTAL 4 900


DOCTOR F 06/07/04 12:45 PM
DOCTOR F 06/07/04 12:45 PM
DOCTOR F 06/07/04 12:30 PM
DOCTOR F 06/07/04 12:15 PM
DOCTOR F 06/07/04 12:00 PM
DOCTOR F 06/07/04 11:30 AM
DOCTOR F 06/07/04 11:00 AM
DOCTOR F 06/07/04 10:45 AM
DOCTOR F 06/07/04 10:45 AM
DOCTOR F 06/07/04 10:30 AM
DOCTOR F 06/07/04 10:15 AM
DOCTOR F 06/07/04 10:00 AM
DOCTOR F 06/07/04 9:45 AM
DOCTOR F 06/07/04 9:30 AM
DOCTOR F 06/07/04 9:15 AM
DOCTOR F 06/07/04 9:00 AM
DOCTOR F 06/07/04 8:45 AM
DOCTOR F 06/07/04 8:30 AM
DOCTOR F 06/07/04 8:15 AM
DOCTOR F 06/07/04 8:15 AM
DOCTOR F 06/07/04 7:45 AM
DOCTOR F 06/07/04 7:45 AM
DOCTOR F 06/07/04 7:15 AM
DOCTOR F 06/07/04
DOCTOR F 06/07/04
DOCTOR F 06/07/04
DOCTOR F 06/08/04 12:45 PM
DOCTOR F 06/08/04 12:15 PM
DOCTOR F 06/08/04 12:00 PM
DOCTOR F 06/08/04 11:30 AM
DOCTOR F 06/08/04 10:45 AM
DOCTOR F 06/08/04 10:15 AM
DOCTOR F 06/08/04 9:45 AM
DOCTOR F 06/08/04 9:30 AM
DOCTOR F 06/08/04 9:15 AM
DOCTOR F 06/08/04 9:00 AM
DOCTOR F 06/08/04 8:45 AM
DOCTOR F 06/08/04 8:30 AM
DOCTOR F 06/08/04 8:15 AM
DOCTOR F 06/08/04 8:00 AM
DOCTOR F 06/08/04 7:30 AM
DOCTOR F 06/08/04 7:15 AM
DOCTOR F 06/08/04
DOCTOR F 06/08/04
DOCTOR F 06/08/04
DOCTOR F 06/08/04
DOCTOR F 06/09/04 1:00 PM
DOCTOR F 06/09/04 12:30 PM
DOCTOR F 06/09/04 12:15 PM
DOCTOR F 06/09/04 12:00 PM
DOCTOR F 06/09/04 11:30 AM
DOCTOR F 06/09/04 10:45 AM
DOCTOR F 06/09/04 10:15 AM
DOCTOR F 06/09/04 10:00 AM
DOCTOR F 06/09/04 8:45 AM
DOCTOR F 06/09/04 8:15 AM
DOCTOR F 06/09/04 7:45 AM
DOCTOR F 06/09/04 7:15 AM
DOCTOR F 06/09/04
DOCTOR F 06/09/04
DOCTOR F 06/10/04 1:00 PM
DOCTOR F 06/10/04 11:45 AM
DOCTOR F 06/10/04 11:30 AM
DOCTOR F 06/10/04 11:00 AM
DOCTOR F 06/10/04 10:45 AM
DOCTOR F 06/10/04 10:30 AM
DOCTOR F 06/10/04 10:15 AM
DOCTOR F 06/10/04 10:00 AM
DOCTOR F 06/10/04 9:45 AM
DOCTOR F 06/10/04 9:30 AM
DOCTOR F 06/10/04 9:15 AM
DOCTOR F 06/10/04 9:15 AM
DOCTOR F 06/10/04 8:45 AM
DOCTOR F 06/10/04 8:00 AM
DOCTOR F 06/10/04 8:00 AM
DOCTOR F 06/10/04 7:45 AM
DOCTOR F 06/10/04 7:15 AM
DOCTOR F 06/11/04 1:00 PM
DOCTOR F 06/11/04 12:15 PM
DOCTOR F 06/11/04 12:00 PM
DOCTOR F 06/11/04 11:15 AM
DOCTOR F 06/11/04 10:45 AM
DOCTOR F 06/11/04 10:30 AM
DOCTOR F 06/11/04 10:30 AM
DOCTOR F 06/11/04 10:15 AM
DOCTOR F 06/11/04 9:45 AM
DOCTOR F 06/11/04 9:30 AM
DOCTOR F 06/11/04 9:00 AM
DOCTOR F 06/11/04 8:45 AM
DOCTOR F 06/11/04 8:30 AM
DOCTOR F 06/11/04 8:15 AM
DOCTOR F 06/11/04 7:45 AM
DOCTOR F 06/11/04 7:30 AM
DOCTOR F 06/11/04 7:15 AM
DOCTOR F 06/11/04 7:15 AM
DOCTOR F TOTAL 5 1500


jennifer.giemza@uwmf.wisc.edu

 

[lbass]

Thanks, that helped. Do you have the option of eliminating nulls from your report? One problem is that if nulls are being translated as date(0,0,0) they will be the minimum for the date group. So if you can, you should add the following to your record selection formula:

not isnull({table.appt_time})

The other issue is that some of these doctors are working evenings. Your formulas don't account for that AND the approach which uses a maximum won't work, since the maximum for these doctors is out of the afternoon range. I think you should change your formulas and use the following method:

//{@morningtime} to be placed in the detail section:
if isnull({@appt_time}) then 0 else
if {@appt_time} in time(0,0,0) to time(11,59,59) then 1 else 0

//{@afternoontime} to be placed in the detail section:
if isnull({@appt_time}) then 0 else
if {@appt_time} in time(12,0,0) to time(16,59,59) then 1 else 0

//{@evening time} to be placed in the detail section:
if isnull({@appt_time}) then 0 else
if {@appt_time} in time(17,0,0) to time(23,59,59) then 1 else 0

Then create the following formulas for your group header (or footer):
//{@morningcount}:
if sum({@morningtime},{table.date}) > 0 then 1 else 0

//{@morningminutes}:
if sum({@morningtime},{table.date}) > 0 then 300 else 0

Repeat the last two formulas for afternoons and evenings. Then use variables to sum these at the doctor and city levels.

Let me know if you need further help.

-LB