Client and Matter Exception Rates

Client and matter exception rates are one of the more difficult areas to manage in Enterprise. This can be complicated by the fact in versions prior to 3.10, there is no end date on the records. In order to end an exception, you had to enter a new line with a zero percent or dollar deviation. Thankfully this has been corrected in 3.10. For those still on prior versions, this bit of code gives you a full view of exceptions for clients and matters. In an effort to make the data a bit more manageable, there is a department variable that limits the results of the query to a single department.

This is probably as good a time as any to point out the merits (and limitations) of common table expressions (CTE) as opposed to sub-queries. You can see in the top portion of the query below that there is a "WITH" expression. Using a WITH expression creates a common table expression from a query and gives it a name, in this case maxmex. You can then call this CTE later in your query as if it were a table. However, note in the client section that I've used subqueries instead of a CTE. WITH expressions can only be used at the beginning of a query, and a previous query/statement must be ended with a semicolon before a WITH expression can be used. Since the UNION ALL is a continuation of the query, we can't use a WITH at that point. However, in my opinion, CTEs are much easier to read and understand than sub-queries, so I highly recommend their use.

DECLARE @Dept VARCHAR(2);
SET @Dept = '15';


WITH maxmex AS
 (
 SELECT mrmatter
 ,mrtk
 ,mrtitle
 ,MAX(mreffdate) as 'maxdate'
 FROM mexrate
 WHERE mrccode ='!'
 GROUP BY mrmatter,mrtk,mrtitle
 ),
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.tkrt01 AS tkrt01
 ,b.tkrtcur AS 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.tkrt01,b.tkrtcur
 )
SELECT 
client.clnum AS 'Client #'
,client.clname1 AS 'Client Name'
,mexrate.mrmatter AS 'Matter #'
, matter.mdesc1 AS 'Matter Desc'
, deptlab.head1 AS 'Matter Dept'
, CASE WHEN mexrate.mrtitle='!' THEN mexrate.mrtk ELSE mexrate.mrtitle END AS 'Exception'
, CASE WHEN mexrate.mrtitle='!' THEN tk2.tklast+', '+tk2.tkfirst ELSE 'ALL' END AS 'Exception Name'
, CASE WHEN mexrate.mrtitle='!' THEN tk2.tktitle ELSE mexrate.mrtitle END AS 'Exception Title'
, CASE WHEN mexrate.mrtitle='!' THEN dept2.head1 ELSE 'ALL' END AS 'Exception Dept'
, CASE WHEN mexrate.mrtitle='!' THEN ldesc ELSE 'ALL' END AS 'Exception Loc'
, mexrate.mreffdate
, mexrate.mrrate
, mexrate.mrdevper
, mexrate.mrdevdol
, mexrate.mrrtnum
, rates.tkrt01
, rates.tkrtcur
--, matter.mbillaty
--, deptlab_billatty.head1
--, matter.mclosedt
FROM   mexrate mexrate 
INNER JOIN matter matter  ON mexrate.mrmatter=matter.mmatter
INNER JOIN deptlab deptlab ON matter.mdept=deptlab.delcode
INNER JOIN maxmex maxmex ON mexrate.mrmatter=maxmex.mrmatter AND mexrate.mrtk=maxmex.mrtk AND mexrate.mrtitle=maxmex.mrtitle AND mexrate.mreffdate=maxmex.maxdate
INNER JOIN timekeep timekeep ON matter.mbillaty=timekeep.tkinit
LEFT OUTER JOIN timekeep tk2 ON maxmex.mrtk=tk2.tkinit
LEFT OUTER JOIN deptlab dept2 ON tk2.tkdept=dept2.delcode
LEFT OUTER JOIN location ON tk2.tkloc=locode
INNER JOIN deptlab deptlab_billatty ON timekeep.tkdept=deptlab_billatty.delcode
INNER JOIN client ON mclient=clnum
LEFT OUTER JOIN rates ON mexrate.mrtk=rates.tkrinit AND rates.tkrdate2='12/31/2025'
WHERE  matter.mclosedt IS NULL 
AND (mexrate.mrtk='!' OR mexrate.mrtk IN (SELECT tkinit FROM timekeep WHERE tktmdate IS NULL)) 
AND (mrdevper<>0 OR mrdevper IS NULL)
AND (mrdevdol<>0 OR mrdevdol IS NULL)
AND matter.mdept=@Dept

UNION ALL

-------------------------------------------------------------------------------------------
-- Client Exceptions
-------------------------------------------------------------------------------------------

SELECT cexrate.crclient AS 'Client #'
, client.clname1 AS 'Client Name'
, 'All' AS 'Matter #'
, 'All' AS 'Matter Desc'
, 'All' AS 'Matter Dept'
, CASE WHEN cexrate.crtitle='!' THEN cexrate.crtk ELSE cexrate.crtitle END AS 'Exception'
, CASE WHEN cexrate.crtitle='!' THEN tk2.tklast+', '+tk2.tkfirst ELSE 'ALL' END AS 'Exception Name'
, CASE WHEN cexrate.crtitle='!' THEN tk2.tktitle ELSE cexrate.crtitle END AS 'Exception Title'
, CASE WHEN cexrate.crtitle='!' THEN dept2.head1 ELSE 'ALL' END AS 'Exception Dept'
, CASE WHEN cexrate.crtitle='!' THEN ldesc ELSE 'ALL' END AS 'Exception Loc'
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crrtnum
, rates.tkrt01
, rates.tkrtcur
FROM   cexrate 
INNER JOIN 
 (SELECT crclient
 ,crtk
 ,crtitle
 ,MAX(creffdate) as 'maxdate'
 FROM cexrate
 WHERE crccode ='!'
 GROUP BY crclient,crtk,crtitle) AS
maxcex ON cexrate.crclient=maxcex.crclient AND cexrate.crtk=maxcex.crtk AND cexrate.crtitle=maxcex.crtitle AND cexrate.creffdate=maxcex.maxdate
LEFT OUTER JOIN timekeep tk2 ON maxcex.crtk=tk2.tkinit
LEFT OUTER JOIN deptlab dept2 ON tk2.tkdept=dept2.delcode
LEFT OUTER JOIN location ON tk2.tkloc=locode
INNER JOIN client ON cexrate.crclient=client.clnum
LEFT OUTER JOIN
 (
 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.tkrt01 AS tkrt01
 ,b.tkrtcur AS 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.tkrt01,b.tkrtcur
 ) AS rates ON cexrate.crtk=rates.tkrinit AND rates.tkrdate2='12/31/2025'

WHERE  (cexrate.crtk='!' OR cexrate.crtk IN (SELECT tkinit FROM timekeep WHERE tktmdate IS NULL)) 
AND (crdevper IS NULL OR crdevper <> 0)
AND (crdevdol IS NULL OR crdevdol <> 0)
AND EXISTS (SELECT * FROM matter WHERE mclient=clnum AND mdept=@Dept AND mclosedt IS NULL)

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

Accounts Payable Annual Activity and Balance

Here's a quick and dirty little query that you can run at the beginning of the year to see a summary of the prior year's accounts payable activity and current balances by vendor. Transactions are converted to firm currency using the average method for the month of the transaction, and balance is converted to firm currency using the daily rate as of the end of the year. Note that the balance table is outer joined, so not all balances will be returned, just ones with prior payment activity. If you want both, you would need to do some fancy footwork with a full outer join and some coalescing, which is beyond the scope of this post =).

SELECT
ap.apnum
,ap.apname
,SUM(CASE WHEN (apyr=2013) THEN apntpr/trancur.cdrate ELSE 0 END) AS '2013'
,SUM(CASE WHEN (apyr=2012) THEN apntpr/trancur.cdrate ELSE 0 END) AS '2012'
,ISNULL(apbal.apbal/balcur.cdrate,0) AS 'Balance'
,apamt.currency AS 'Currency'
FROM ap
INNER JOIN apamt ON ap.apnum=apamt.apnum
INNER JOIN currates trancur ON DATEADD(mm,apper-1,DATEADD(yy,apyr-1900,0)) BETWEEN trancur.cddate1 AND trancur.cddate2
 AND trancur.trtype='A'
 AND trancur.curcode=apamt.currency
LEFT OUTER JOIN apbal ON ap.apnum=apbal.apnum AND apbal.currency=apamt.currency
LEFT OUTER JOIN currates balcur ON '12/31/2013' BETWEEN balcur.cddate1 AND balcur.cddate2
 AND balcur.trtype='D' AND balcur.curcode=apbal.currency
GROUP BY ap.apnum,ap.apname,apbal.apbal,apamt.currency,balcur.cdrate
ORDER BY SUM(CASE WHEN (apyr=2013) THEN apntpr ELSE 0 END) DESC

Matters with No Recent Activity

This is similar to yesterday's Client post, but for matters and with a one year qualification. It identifies "Open" matters opened more than a year ago with no activity in the past year.

SELECT
mmatter
,mdesc1
,mopendt
FROM
matter
WHERE
mstatus = 'OP'
AND mopendt < DATEADD(yy,-1,GETDATE())
--No new time
AND NOT EXISTS (SELECT * FROM timecard WHERE mmatter=tmatter AND tworkdt >= DATEADD(yy,-1,GETDATE()))
--No new costs
AND NOT EXISTS (SELECT * FROM cost WHERE mmatter=cmatter AND cdisbdt >= DATEADD(yy,-1,GETDATE()))
--No new trust
AND NOT EXISTS (SELECT * FROM trsttran WHERE mmatter=trmatter AND trdate >= DATEADD(yy,-1,GETDATE()))
--No new invoices or payments
AND NOT EXISTS (SELECT * FROM ledger WHERE mmatter=lmatter AND ltradat >= DATEADD(yy,-1,GETDATE()))
--No A/R or WIP
AND NOT EXISTS (SELECT * FROM inqsnap WHERE icode='M' AND ivalue=mmatter AND (ar_fees+ar_cost+ar_other+ar_unall+unb_hours+unb_fees+unb_cost)<>0)

Clients with No Recent Activity

The following is a simple query to identify clients that have had no activity over the past 2 years. It looks at "Current" clients opened more than two years ago.

SELECT
clnum
,clname1
,clopendt
FROM
client
WHERE
clstatus = 'C'
AND clopendt < DATEADD(yy,-2,GETDATE())
--No new time
AND NOT EXISTS (SELECT * FROM matter INNER JOIN timecard ON mmatter=tmatter WHERE mclient=clnum AND tworkdt >= DATEADD(yy,-2,GETDATE()))
--No new costs
AND NOT EXISTS (SELECT * FROM matter INNER JOIN cost ON mmatter=cmatter WHERE mclient=clnum AND cdisbdt >= DATEADD(yy,-2,GETDATE()))
--No new trust
AND NOT EXISTS (SELECT * FROM matter INNER JOIN trsttran ON mmatter=trmatter WHERE mclient=clnum AND trdate >= DATEADD(yy,-2,GETDATE()))
--No new invoices or payments
AND NOT EXISTS (SELECT * FROM matter INNER JOIN ledger ON mmatter=lmatter WHERE mclient=clnum AND ltradat >= DATEADD(yy,-2,GETDATE()))
--No new matters
AND NOT EXISTS (SELECT * FROM matter WHERE mclient=clnum AND mopendt >= DATEADD(yy,-2,GETDATE()))
--No A/R or WIP
AND NOT EXISTS (SELECT * FROM inqsnap WHERE icode='MC' AND ivalue=clnum AND (ar_fees+ar_cost+ar_other+ar_unall+unb_hours+unb_fees+unb_cost)<>0)

VBA - Controlling Form Appearance via User Access

We've added some custom functionality to several of our Elite forms recently using Visual Basic for Applications (VBA). As with many things Elite, there's more than one way to accomplish the goal. In this case, we only wanted certain users to have access to the custom functionality. The popular method that is included in your Elite documentation would be to set the users up in a group and make the VBA changes just for that group. I didn't particularly want to go down that route for several reasons, so what I settled on was controlling the visibility of the custom items via a user access setting. In the case below, it is u85: the ability to access Queues in Reports & Queues. The custom control is set to visible on the form, and if the user does not have access to queues, then the control is hidden.

Private Sub EisVBAForm_FormInitialize()

'Set SYS Level to 1 and User to 0
'Elite setups give access if User Level is higher than SYS
'so we're starting with denying access
'Then evaluating their actual access levels
SysLevel = 1
UserLevel = 0

'Get SYS Access Level
strSQL = "SELECT u85 FROM uaccess WHERE uname='SYS'"

Set rsUaccess = DB.OpenResults(strSQL)

If rsUaccess.Count = 0 Then
    MsgBox "User Access Error." & vbCrLf & "Contact System Administrator", vbOKOnly + vbExclamation, "User Access Error"
Else
    SysLevel = rsUaccess.Value(0)
    
'Get User Access Level
    strSQL = "SELECT u85 FROM uaccess WHERE uname='" & Trim(UserID) & "'"
    
    Set rsUaccess = DB.OpenResults(strSQL)
    
    If rsUaccess.Count > 0 Then
        UserLevel = rsUaccess.Value(0)
    End If
    
'Compare the two, if User is lower than Sys, Hide a control on the form
    If UserLevel < SysLevel Then
        cmdDistProf.Visible = False
    End If
End If

Set rsUaccess = Nothing

End Sub

Audit Modification

Here is an example of how to manipulate the audit modification tables to pull specific scenarios you want to monitor. The code below is specifically looking for a change in timekeepers for a specific type of multi-timekeeper where the effective date is prior to the current month.

SELECT
au.audate AS 'Date Changed'
,au.auuser AS 'Elite User'
,tklast+', '+tkfirst AS 'User Full Name'
,au.aukeyvalue1 AS 'Matter #'
,mdesc1 AS 'Matter Description'
,mopendt AS 'Open Date'
,au.auindex AS 'Audit Index'
,ad.audesc AS 'New Value'
,ap.audesc AS 'Previous Value'
,tkemail AS 'User Email'
FROM auditmod au
INNER JOIN auditmodd ad ON au.auindex=ad.auindex AND ad.autype = 'N' AND ad.audesc LIKE 'SOURCE%'
INNER JOIN auditmodd ap ON au.auindex=ap.auindex AND ap.autype = 'P' AND ap.audesc LIKE 'SOURCE%'
INNER JOIN matter ON aukeyvalue1=mmatter
INNER JOIN uaccess ON au.auuser=uaccess.uname
INNER JOIN timekeep ON uaccess.wvtkinit=timekeep.tkinit
WHERE autable = 'MTKPER'
AND audate >= GETDATE()-2
AND ad.audesc LIKE '%12/31/2025%'
AND mopendt < DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
AND MONTH(au.audate) <> SUBSTRING(ad.audesc,CHARINDEX(' ',ad.audesc),CHARINDEX('/',ad.audesc,CHARINDEX(' ',ad.audesc)+1)-CHARINDEX(' ',ad.audesc))
AND LEFT(RIGHT(ap.audesc,CHARINDEX(' ',REVERSE(ap.audesc))+4),4)
 <> LEFT(RIGHT(ad.audesc,CHARINDEX(' ',REVERSE(ad.audesc))+4),4)
ORDER BY au.auindex