Thursday, December 2, 2010

Calculating a Real Billing Realization

One potential issue with billing realization within Elite is that worked values are set based on the rate codes and exception rates in effect on each matter. This is great for certain purposes, but it's not as handy when trying to compare billed values to a "standard" or "rack" rate. While some firms have chosen to utilize the Accounting Information tab in Timekeeper Master to keep track of standard rates over time, many don't and simply use the Rates tab. This presents a problem with comparing worked values and billed values to a "standard" or "rack" rate for each timekeeper. The following query mostly solves this issue by comparing each timecard worked to the "standard" or "rack" rate in effect for that timekeeper at the time the timecard was worked. A true billing realization can then be calculated.

One of the neat things about this query is the method used to "create" an ending effective date for each record in the rate table. By default, this table only has a beginning effective date. Therefore, by joining the table to itself we are able to match the next highest effective date for a record and subtract a day from it to arrive at an ending date for that record.

Note: While this query is correctly configured to take multi-currency into account, it is assuming that everyone has a US Dollar rate. If that's not the case for your firm, modifications will need to be made. Also, for this example, Rate Code 2 is considered the "standard" or "rack" rate.

WITH rates AS
(
SELECT b.tkinit AS tkrinit
,b.tkeffdate AS tkrdate1
,MIN(CASE WHEN e.tkeffdate IS NULL THEN '1/1/2026' ELSE e.tkeffdate END)-1 AS tkrdate2
,b.tkrt02
,b.tkrtcur
FROM timerate b
LEFT OUTER JOIN timerate e ON b.tkinit=e.tkinit AND e.tkeffdate > b.tkeffdate AND b.tkrtcur=e.tkrtcur
GROUP BY b.tkinit,b.tkeffdate,b.tkrt02,b.tkrtcur
)
SELECT
head1 AS 'Department'
,tkinit AS 'TK #'
,tklast+', '+tkfirst AS 'Timekeeper'
,SUM(tworkhrs) AS 'Worked Hrs'
,CAST(SUM(CASE WHEN rates.tkrt02 iS NULL THEN tworkhrs*dorates.tkrt02 ELSE tworkhrs*rates.tkrt02/cdrate END) AS DECIMAL(16,2)) AS 'R2 Fees'
,CAST(SUM(tworkdol/cdrate) AS DECIMAL(16,2)) AS 'Worked Fees'
,CAST(SUM(tbilldol/cdrate) AS DECIMAL(16,2)) AS 'Billed Fees'
FROM
timecard
INNER JOIN timekeep ON ttk=tkinit
INNER JOIN deptlab ON tkdept=delcode
INNER JOIN periodt ON tbiper=pe
INNER JOIN matter ON tmatter=mmatter
INNER JOIN currates ON mcurrency=curcode AND pebedt BETWEEN cddate1 AND cddate2 AND trtype='A'
LEFT OUTER JOIN rates ON rates.tkrinit=tkinit AND tworkdt BETWEEN rates.tkrdate1 AND rates.tkrdate2 AND mcurrency=rates.tkrtcur
LEFT OUTER JOIN rates dorates ON dorates.tkrinit=tkinit AND tworkdt BETWEEN dorates.tkrdate1 AND dorates.tkrdate2 AND dorates.tkrtcur='DO'
WHERE YEAR(pebedt)='2010'
AND pebedt < '12/1/10'
AND tstatus NOT IN ('E','NBP')
GROUP BY tkdept,head1,tkinit,tklast,tkfirst
ORDER BY tkdept,head1,tklast
The total results for Worked Hours, Worked Fees and Billed Fees should tie back to Timekeeper and Matter Stats in Inquiry.

No comments:

Post a Comment