Calculate a Static number

[dezmundw15]

I am in need of some help relative to a static number I have set in a table.  I am trying to calculate appointments.

I have noted in a table all of the Providers the number of appointments they should have per week.

For Example:

Week of 4.30 - 5.06 = 31 appointments - - - Saw 18 patients

Week of 5.07 - 5.13 = 31 appointments - - - Saw 25 patients

Week of 5.14 - 5.20 = 31 appointments - - - Saw 20 patients

I want to calculate Productivity for a Provider:

Total of 93 appointments and Saw 63 patients

How do I add the static Appointment in a Table to sum and then divide the count of patients seen. 

I successfully determined my fill rate:

Sum ([STATUS]='Seen')*-1/Count([PATIENT/DOB])....however there is another Status of 'Pending Arrival' that I want to add, when I try to add it comes back with nothing ( - ).

Can someone help with both of these situations?

Thanks,

Desmond

[stevedark]

In that case you definitely want to crosstable values, so;

CROSSTABLE (Day, Availability, 2)

LOAD
  Clinician,

  [Total Availability_Productivity] as [Total Availability],

  Monday,

  Tuesday,

  Wednesday,

  Thursday,

  Friday

FROM ... your data source ...

;

You will then find that you can total availability across days, or pick individual week days.

Presume that your logic wants to say that if no appointments happened on a day that all appointments are ignored, but if one patient is seen then you assume the clinician is there and therefore that would lower the ratio?

What you probably need to do is join the availability table on clinician and day of the week.  You can do that with a composite key, you can create that on the crosstable above with a resident load, so...

tmpAvail:

CROSSTABLE (Day, Availability, 2)

LOAD
... as above ...

FROM ... your data source ...

;

Avail:

LOAD

  Left(Day, 3) & Clinician as ClinicianDayKey,

  Availability

RESIDENT tmpAvail

;

DROP TABLE tmpAvail;

Then when you load the appointments table you would need to create the same key, something like this:

LOAD

    if(match(STATUS,'Seen','Pending Arrival') > 0, WeekDay(AppointmentDate) & Clinician, null()) as ClinicianDayKey,

   Clinician,

   Day,

   STATUS,

   ... rest of data load ...

This way any appointment that happened would associate with the full number of appointments for that clinician for that day of the week.  If no appointments happen then that key would be missing, so the number of appointments would then not happen.

Hopefully that points you in the right direction for the next step?