I'm trying to create a detail/summary report on all customer service phone activity like this:

Employee name: John Jones – Date 08/14/2009

Time Attempts Outbound Contact Inbound Total
of Day Contacts % Contacts Contacts
8-9:00 1 1 100% 2 3
9-10:00 1 1 100% 0 1
10-11:00 0 0 0% 1 1
11-12:00 10 5 50% 0 5
12-13:00 12 9 75% 1 10
Totals 24 16 66% 4 19

Attmpts/Hr Contacts/Hr Phone Prod Time
Daily 5.45 4.28 6:47:00
MTD Avg 3.84 2.96 6:33:45

Employee name: Lisa Kline – Date 08/14/2009
…

The Daily and MTD Avg stats require joining to two other tables for additional Daily
data needed in the computations.

The detail part is easy with a BY HOUR and COMPUTE. What I can't figure out
is how to bring the other two tables to calculate the Daily and MTD stats. I tried
MATCHing all the tables together but the BY fields don't match. I don't think I
can MORE it because the results of the two sections are so different.

Any help would be appreciated.

This message has been edited. Last edited by: Kerry,

If the data in the BY fields is essentially the same, but in different formats, then creating DEFINE fields for your match routine may help. If the BY fields have the same format, but different names, then using AS phrases or DEFINEs to make the names the same would also help.

If these are not the issues, then you may have to create some hold files first to get the data from all sources into formats that you can work with.

Another approach is if you can get a report for the detail portion to work, and you can get a report the other part to work, you can save these reports as hold files.

For each hold file insert a dummy DEFINE field:

 
    DEFINE FILE DETAIL_DATA
    DUMMY/A1 ='A';
    END

    TABLE FILE DETAIL_DATA
    ...
    ON TABLE HOLD AS ___
    END

For the other files, the value of DUMMY can be 'A' or something else, depending on the logic you need.

This creates a field common to all files to join or match on.

Back in my mainframe FOCUS days, when I had to create reports from data sources that had nothing in common, this dummy field approach worked very well.


Another approach is join the data files to another file that has fields common to each of the data files

For example, to get a list of all the course titles each employee took, you can't join EMPDATA to COURSE directly -- they have no fields in common. But if you join the PIN field in EMPDATA to the PIN field in TRAINING, and the COURSECODE field in that to the COURSECODE field in COURSE, then you can get the desired report:

 
    JOIN PIN        IN empdata TO MULTIPLE PIN        IN training AS J0
    JOIN COURSECODE IN empdata TO UNIQUE   COURSECODE IN course   AS J1

    TABLE FILE EMPDATA
        PRINT
            FIRSTNAME
            PIN
            CTITLE
        BY LASTNAME
    END
 

Hope this helps,

John

This message has been edited. Last edited by: JohnB,

Sorry, I got pulled away for a couple weeks. JohnB, thanks for your suggestions.