Count based upon Conditions

[crystalclear01]

crystalclear01 (MIS) Jan 17, 2005
All, I have a brain wrecker...

CR 9 Oracle DB, 1 table: CHART. Looks like this:

CHART# SEEN BY CLASS
12345 Jack Doctor
12345 Jill Nurse
11445 Nancy Doctor
56487 Joe Doctor
46898 Jack Doctor
46898 Diane Nurse
67678 Jill Nurse

Trying to count with a twist:

1. If the patient has ben seen only by a doctor, give the doctor credit.
2. If the patient has been seen only by a nurse, give the nurse credit.
3 If the patient has been seen both by a Doctor and a Nurse, give the Nurse credit

So in the above scenario the count would be as follows:
JACK: 0
JILL: 2
NANCY: 1
JOE: 1
Diane: 0

(The real table is about 12,000)

I've tried maybe splitting the table for duplicates or inserting a running total. no luck so far.
Any help would be greatly appreciated.

 

[madelca100]

Here's a thought. (I don't know how to accomplish this in Crystal, but in Access I would try the following.)

Create a field that concatenates the first letter of Class with the Seen By. I call it CSB.

It will look like this:

DJack
NJill
DJoe
etc.

If you take the max(CSB) for each chart # and you will have either a Doc when there is only a Doc or a Nurse - for all other cases. (If you have more than D(oc) and N(urse) then assign a letter so that low priority gets an A and highest get Z.)

Substringing CSB will allow you to sort and count by Seen By.

mike

 

[Madawc]

One method would be to group patients by 'SEEN BY'. The problem is to avoid adding for doctors when a nurse has also seen the patent - I'm assuming that it can only be one of each, in your system. What you could do is add the data twice, the second time as an 'alias' linked by patient. So in fact the grouping would be
Group 1 - 'SEEN BY' (Table A)
Group 2 - 'CHART' (Table B)
Do group 2 totals for doctor and nurse. Then check these at Group 1 to get the values for the doctor or nurse. I've not actually done something like this, and you might find you needed to use formulas with variables

Also consider tha alternative of writing something in SQL, something simple that would do a summary for patients, excluding 'doctor' entires when a nurse had also seen them

Madawc Williams (East Anglia, UK)

 

[Madawc]

I've just realised there's a simplification. Group as I said, but do a 'summary total' giving the minimum for 'CLASS' on Table B. This will be 'Doctor' if there is one, and summary totals can be used more freely.

Right-click on a field and choose Insert to get a choice of Running Total, Summary and Grand Total. Or else use the Field Explorer, the icon that is a grid-like box.
It is also possible to get get totals using a Formula Field, which can contain a Variable or a Directly Calculated Total.
Running totals allow you to do clever things with grouping and formulas. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.
Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say.

Madawc Williams (East Anglia, UK)