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

Extend & Imaging - Vouchers not Imaged

Another way you can use Extend and Image Connect together is to identify which transactions in Elite do not have a corresponding barcode attached. The query below looks at vouchers in Elite and compares it to Image Connect to see if a barcode has been attached. If not, the voucher and user will appear in the results (and therefore receive an email from Extend stating as such). This will help ensure all transactions are properly scanned. The query below assumes use of AD authentication which should allow you to simply add on your domain to the username to arrive at the email address of the user. If you don't use AD authentication, I'd suggest using an user access UDF field for this purpose.

SELECT vo_id
,fmsbtid
,ap.apnum
,ap.appynm
,votrdt
,ufullname
,ufullname+'@yourdomain.com' AS email
,voamt
FROM apvo
INNER JOIN fmsbatch ON vobtid=fmsbtid AND trtype='VO'
INNER JOIN ap ON apvo.apnum=ap.apnum
INNER JOIN usmaster ON fmsbatch.opuserid=usmaster.userid
WHERE NOT EXISTS (SELECT * FROM [YourImagingDBServer].[IMAGING].[dbo].[Profiles] WHERE CAST(vo_id AS VARCHAR(12))=TransID1 AND AppTypeID=28 AND ProfileToAppStatus=1)
AND votrdt >= '12/1/10'
AND voamt > 0
ORDER BY opuserid,votrdt

Extend & Image Connect

Extend is one of the most useful tools in the Enterprise arsenal. Image Connect (or whatever imaging solution is in use) is also a very helpful tool for documenting transactions. Below, I've married the two to email out items scanned to the matter number for management to see what items are being documented at that level. Note that since your Imaging db is likely on a different server than your Elite db, you'll need to create a linked server on your Elite db server to access the remote Imaging server. There are a few assumptions made here: 1. The document is scanned on the same day the profile is created 2. The user has access to the file paths containing the images on the Image Connect application server.

SELECT Operators.FullName
,AppTypes.Description AS AppDesc
,Profiles.ProfileID
,Profiles.TransID1
,Profiles.Description AS ProfDesc
,Profiles.CreateDate
,Barcodes.BarcodeValue
,'<a href="file:///'+[ImagePath].[Path]+''+ImagePath.TempPath+''+CAST(Images.BarcodeGUID AS VARCHAR(255))+'.'+Images.FileExtension+'">View Image</a>'
FROM [imagingserver].[imaging].[dbo].[Profiles]
INNER JOIN [imagingserver].[imaging].[dbo].[Images] ON Profiles.ImageID=Images.ImageID
INNER JOIN [imagingserver].[imaging].[dbo].[ImagePath] ON Images.ImageID=ImagePath.ImageID
INNER JOIN [imagingserver].[imaging].[dbo].[Operators] ON Profiles.UserID=Operators.OperatorID
INNER JOIN [imagingserver].[imaging].[dbo].[AppTypes] ON Profiles.ApptypeID=Apptypes.AppTypeID
INNER JOIN [imagingserver].[imaging].[dbo].[Barcodes] ON Images.BarcodeGUID=Barcodes.BarcodeGUID
WHERE ImageStored = 1
AND Profiles.AppTypeID ='17'
AND ProfileToAppStatus=1
AND Profiles.CreateDate >= '12/1/10'

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.

Back in the Saddle

So, after a 6 month respite, I have returned and will begin adding new content to the blog once again! The last 6 months have been an exciting time for me and my family, having moved to Philadelphia, PA and joined a new law firm. The most visible changes you will see here include the addition of multi-timekeeper and multi-currency concepts to Enterprise SQL queries, as these are staples of my new employer. Thanks for visiting and I hope you will find the information here helpful!