Measuring deflection rates

Hi folks,

I'm hoping that someone can assist. I work for a customer support organization that has a self-service customer knowledge portal, and I'm wanting to put together a report that shows deflection rates. We have defined 'deflection' as:

When a user has viewed an article, AND rated it within 12 hours of the view datestamp, AND when they have not logged an incident within 24 hours of the view datestamp

I have three tables, one showing articles viewed, who viewed them, and the datestamp of the viewing. The second table shows articles that have been rated, who rated them, and the datestamp of the rating. The final table shows incidents created, who they were created for, and the datestamp of their creation.

Table name: Viewed

Table name: Ratings

Table name: Incidents

I've experimented with a couple different table relationships, based on the user's names and also the datestamps, but haven't been able to get what I wanted. Ultimately I'm looking for a calculated table that shows columns for:

• article viewer
• article viewed id
• article viewed date
• article rated date

Counting the lines of this calculated table should show me total deflections. Based on the sample data above, I'd expect the following to be counted as deflections:

Any assistance would really be appreciated - thanks so much! 🙂