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