CRM: Comparing Time Worked with Time Billed on Resolved Cases

This article applies to CRM 4.0

We’re a technical service company and we manage the work effort needed to resolve issues by using cases and tasks in CRM. At the end of a case, however, the person resolving the case determines how much of the time worked was billable.

On the one hand, we want to be fair and balanced to our clients, but on the other hand we put bread on the table by selling our knowledge and experience by the hour. There’s not an easy way to report on the discrepancies between how much work was done and how much was billed.

Thanks to David Jennaway, I’ve got a handy-dandy T-SQL script that I used to come up with an Excel spreadsheet that pulls the data from a SQL view. I’ve modified his original script somewhat to bring back the Account Name rather than the Incident ID.

select
    i.accountidname,
    i.title,
    ir.actualend,
    max(timespent) as BillableTime,
    isnull(sum(a.actualdurationminutes), 0) as TotalActivityTime

from filteredactivitypointer a
    join filteredincident i on a.regardingobjectid = i.incidentid
    left outer join filteredincidentresolution ir on i.incidentid = ir.incidentid and ir.statecode = 1
    and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1)
where i.statecode = 1

I actually saved this as a view, then called it from an Excel spreadsheet. That lets you pull the data into a pivot table, etc.

You can also then build on this and figure out your top accounts using a script like this -

SELECT
    Accountidname as AccountName,
    COUNT(accountidname) AS Cases,
    SUM(BillableTime) AS TimeBilled,
    SUM(TotalActivityTime) as TimeWorked,
    SUM(TotalActivityTime) - SUM(BillableTime) as Variance
from acc_ResolvedCaseTimeComparison
GROUP BY ACCOUNTIDNAME
ORDER BY TimeBilled DESC

Where myresolvedcaseview is the name that you gave your SQL view. This query brings back each account, the number of resolved cases, the amount of time worked, time billed, and the variance.

Note, you’re running directly against the SQL data, so it depends on specific security rights to the database to see the data.