Billing Volumes by Biller

The following query summarizes the quantity and value of non-reversed of invoices billed by user (biller). This particular query assumes billers are set up as timekeepers. If they are not, replace the tklast + tkfirst with ufullname from the usmaster table (joined to the uaccess table). We also use a UDF to define whether a matter is eBilled (udf index 73 shown below), and results are divided between eBilled and non-eBilled. Also, we define hourly and contigent matters as the presence or absence of a value in the Projected Fees field in master matter (mffee field in the matter table). Results are also split based on this field.

SELECT
tklast+', '+tkfirst AS 'User'
,COUNT(DISTINCT CASE WHEN (udvalue<>'Y' OR udvalue IS NULL) AND mffee IS NULL THEN linvoice ELSE NULL END) AS 'Hourly Non-eBill #'
,SUM(CASE WHEN (udvalue<>'Y' OR udvalue IS NULL) AND mffee IS NULL THEN lamount/cdrate ELSE 0 END) AS 'Hourly Non-eBill $'
,COUNT(DISTINCT CASE WHEN udvalue='Y' AND mffee IS NULL THEN linvoice ELSE NULL END) AS 'Hourly eBill #'
,SUM(CASE WHEN udvalue='Y' AND mffee IS NULL THEN lamount/cdrate ELSE 0 END) AS 'Hourly eBill $'
,COUNT(DISTINCT CASE WHEN mffee IS NULL THEN linvoice ELSE NULL END) AS 'Hourly Total #'
,SUM(CASE WHEN mffee IS NULL THEN lamount/cdrate ELSE 0 END) AS 'Hourly Total $'
,COUNT(DISTINCT CASE WHEN (udvalue<>'Y' OR udvalue IS NULL) AND mffee IS NOT NULL THEN linvoice ELSE NULL END) AS 'Cont Non-eBill #'
,SUM(CASE WHEN (udvalue<>'Y' OR udvalue IS NULL) AND mffee IS NOT NULL THEN lamount/cdrate ELSE 0 END) AS 'Cont Non-eBill $'
,COUNT(DISTINCT CASE WHEN udvalue='Y' AND mffee IS NOT NULL THEN linvoice ELSE NULL END) AS 'Cont eBill #'
,SUM(CASE WHEN udvalue='Y' AND mffee IS NOT NULL THEN lamount/cdrate ELSE 0 END) AS 'Cont eBill $'
,COUNT(DISTINCT CASE WHEN mffee IS NOT NULL THEN linvoice ELSE NULL END) AS 'Cont Total #'
,SUM(CASE WHEN mffee IS NOT NULL THEN lamount/cdrate ELSE 0 END) AS 'Cont Total $'
,COUNT(DISTINCT linvoice) AS 'Total #'
,SUM(lamount/cdrate) AS 'Total $'
FROM ledger
INNER JOIN ledcode ON llcode=lccode
INNER JOIN batch ON bbatch=lbatch
INNER JOIN uaccess ON bop=uname
INNER JOIN timekeep ON wvtkinit=tkinit
INNER JOIN periodt ON lperiod=pe
INNER JOIN matter ON mmatter=lmatter
INNER JOIN location ON mloc=locode
INNER JOIN client ON mclient=client.clnum
INNER JOIN client cr ON client.crelated=cr.clnum
INNER JOIN currates ON mcurrency=curcode AND trtype='A' AND pebedt BETWEEN cddate1 AND cddate2
LEFT OUTER JOIN udf ON lmatter=udjoin AND udfindex=73
WHERE pebedt BETWEEN '1/1/2013' AND '12/31/2013'
AND lcfco IS NOT NULL
AND lzero <> 'R'
GROUP BY tklast,tkfirst
ORDER BY COUNT(DISTINCT linvoice) DESC

I've also included a slightly different query below, which measures total invoice output by biller for the current month.

SELECT  tklast+', '+tkfirst AS 'Biller' 
,peendt 
,COUNT(DISTINCT linvoice) AS 'NumInvs' 
,SUM(CASE WHEN lcfco='F' THEN lamount/cdrate ELSE 0 END) AS 'Fees' 
,SUM(CASE WHEN lcfco='C' THEN lamount/cdrate ELSE 0 END) AS 'Costs' 
,SUM(CASE WHEN lcfco NOT IN ('F','C') THEN lamount/cdrate ELSE 0 END) AS 'Other' 
,SUM(lamount/cdrate) AS 'Total' 
,SUM(lamount/cdrate)/COUNT(DISTINCT linvoice) AS 'AvgInv' 
FROM ledger 
INNER JOIN batch ON lbatch=bbatch 
INNER JOIN ledcode ON llcode=lccode 
INNER JOIN uaccess ON bop=uname 
INNER JOIN timekeep ON wvtkinit=tkinit 
INNER JOIN periodt ON lperiod=pe 
INNER JOIN matter ON lmatter=mmatter 
INNER JOIN currates ON mcurrency=curcode AND currates.trtype='A' AND periodt.pebedt BETWEEN currates.cddate1 AND currates.cddate2 
WHERE lcfco IS NOT NULL 
AND peendt = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)-1  
GROUP BY tklast,tkfirst,peendt

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.

Year-to-Date Billed Fees and Cost with Current Work in Process

Elite provides a host of pre-written reports in it's Reports & Queues module, but most never seem to get at exactly what you need. And then when they do, the client names and matter descriptions are cut off at 20 characters (or some other insane #). The below query will give you year-to-date billed fees and costs as well as the current work in process values by matter for a specific client.

SELECT
mmatter AS 'Matter #',
dbo.udfMattDesc(mmatter) AS 'Matter Description',
ISNULL(t.feewip,0) AS 'Fee WIP',
ISNULL(c.wipcost,0) AS 'Cost WIP',
ISNULL(t.feewip,0)+ISNULL(c.wipcost,0) AS 'Total WIP',
ISNULL(t.billamt,0) AS 'Fees Billed YTD',
ISNULL(c.cbillamt,0) AS 'Costs Billed YTD',
ISNULL(t.billamt,0)+ISNULL(c.cbillamt,0) AS 'Total Billed'
FROM matter
LEFT OUTER JOIN
(
 SELECT tmatter,
 SUM(CASE WHEN tbilldt IS NULL THEN tbilldol ELSE 0 END) AS feewip,
 SUM(CASE WHEN RIGHT(tbiper,2)='09' THEN tbilldol ELSE 0 END) AS billamt
 FROM timecard
 GROUP BY tmatter
) AS t ON mmatter=t.tmatter
LEFT OUTER JOIN
(
 SELECT cmatter,
 SUM(CASE WHEN cbilldt IS NULL THEN cbillamt ELSE 0 END) AS wipcost,
 SUM(CASE WHEN RIGHT(cbiper,2)='09' THEN cbillamt ELSE 0 END) AS cbillamt
 FROM cost
 GROUP BY cmatter
) AS c ON mmatter = c.cmatter
WHERE mclient='998877'