Matter Exception Rate Update

Occasionally we have the need to input matter exception rates for a number of matters for a client, but not all (hence matter exceptions and not client exceptions). To my knowledge, there are at least three ways to accomplish this. 1) Manually input exception rates on all matters that need them (but what if there's 50!). 2) If you're on 3.5 or higher, you can create a matter udf called CEXRATE and input a different client number to utilise that client's exception rates. You could then create a dummy client, load the exception rates once and add this client number to each matter in the CEXRATE udf. 3) You can use the method below.

Step 1 - Input exception rates for one matter. Then view the exception rates in SQL. We want to copy the last row with the effective date of 5/1/2004.

SELECT *
FROM mexrate
WHERE mrmatter='2000-001'

Results

mrmatter	mrtk	mreffdate	mrrate	mrdevper	mrdevdol	mrccode	mrtitle	mrmaximum	mrreason	mrrtnum
2000-001	!	2000-01-01 00:00:00.000	200.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2003-01-01 00:00:00.000	250.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2003-03-01 00:00:00.000	300.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2003-05-01 00:00:00.000	350.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2004-04-01 00:00:00.000	400.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2004-05-01 00:00:00.000	500.000	NULL	NULL	!	Senior Partner	N	NULL	NULL

Step 2 - Create temporary table to hold matters that need the exception rates & populate.

CREATE TABLE ##mexrate1
( mmatter VARCHAR(15));

INSERT INTO ##mexrate1 VALUES ('2000-003')
INSERT INTO ##mexrate1 VALUES ('2000-010')
...
--If there is a systematic way to identify the matters then you can use:
INSERT INTO ##mexrate1
SELECT mmatter
FROM matter
WHERE ...(your criteria)...

Step 3 - Execute cursor update below to cycle through the matters and insert new exception rates

BEGIN TRANSACTION mexrateupdate

Declare c Cursor For Select Distinct mmatter From ##mexrate1
Open c

DECLARE @MatId VARCHAR (15);

Fetch next From c into @MatId

While @@Fetch_Status=0 Begin

INSERT INTO mexrate
SELECT @MatId,
 mrtk,
 mreffdate,
 mrrate,
 mrdevper,
 mrdevdol,
 mrccode,
 mrtitle,
 mrmaximum,
 mrreason,
 mrrtnum
FROM mexrate
WHERE mrmatter = '2000-001' --The original matter to copy
 AND mreffdate = '5/1/2004' --The exception rates to copy

   Fetch next From c into @MatId
End

Close c
Deallocate c

Step 4 - Use SQL to view a matter that was updated & make sure everything looks good

SELECT *
FROM mexrate
WHERE mrmatter = '2000-010'

Step 5 - Check the matter in Elite to make sure everything looks ok.
Step 6 - If it's all good, commit the transaction (if not, rollback)

ROLLBACK TRANSACTION mexrateupdate --if it's bad
COMMIT TRANSACTION mexrateupdate --if it's good

Step 7 - Explicitly drop the temporary table

DROP TABLE ##mexrate1

Step 8 - Be thankful you didn't have to manually input 25 exception rates for 75 matters...


DISCLAIMER - As always, test liberally in a test environment and deploy cautiously.

Missing Files in Whitehill

For those that use Elite Document Studio by Whitehill/Skywire/Oracle Legal (whatever it's called these days) there's several moving parts that can wind up not working properly. One of these is that the document record is created in the Document_Catalog table, but the actual file doesn't make its way to the correct folder. This results in an error in Elite when trying to access an invoice pdf. So what do you do when you have a host of files missing from your bill directory, but the records are already in the Document_Catalog table (especially if only 1 of 4 converters wasn't functioning properly)?

Well, here's a solution. If you have any ideas on how to improve this, or re-write it altogether to be more efficient or effective, please share!

The first section of code here creates a temporary table and inserts into the temporary table the list of files of the targeted directory. In this case, we're looking at the bill directory on the Elite application server. You'll need to modify this path according to your environment. One note: the collation in the CREATE TABLE needs to be the same as the collation of your Document_Catalog table otherwise you may get errors when joining the two.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Drop temporary table if it already exists
IF OBJECT_ID(N'tempdb.dbo.#DirContents', N'U') IS NOT NULL 
DROP TABLE tempdb.dbo.#DirContents;
GO

--Create temporary table to store contents of directory
CREATE TABLE #DirContents (
 Line VARCHAR(512) COLLATE SQL_Latin1_General_CP1_CS_AS NULL)

DECLARE  @PathName       VARCHAR(256) ,
         @CMD            VARCHAR(512) 

SET @PathName = '\\eliteappserver\elite\home\bill\'
 
SET @CMD = 'DIR ' + @PathName + ' /TC'
 
PRINT @CMD

INSERT INTO #DirContents
EXEC MASTER..xp_cmdshell   @CMD

The results from above should appear like this:

Database name 'tempdb' ignored, referencing object in tempdb.
DIR \\eliteappserver\elite\home\bill\ /TC

(32568 row(s) affected)

By taking a quick look at the temporary table you should see this:

Line
Volume in drive \\eliteappserver\elite\home\bill is Data_LUN72
Volume Serial Number is ABCD-E123
NULL
Directory of \\eliteappserver\elite\home\bill
NULL
07/17/2009 07:34 PM <DIR> .
07/17/2009 07:34 PM <DIR> ..
10/06/2008 01:09 PM 41,270 000001.000001.481826.10062008.pdf
09/03/2008 01:00 PM 42,434 000567.000026.471323.06052008.pdf
09/03/2008 01:00 PM 42,680 000567.000026.473887.07072008.pdf

These next few lines of code will strip out the non-file lines from the temporary table.

DELETE
FROM   #DirContents
WHERE  Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Line LIKE '%<DIR>%'
OR Line is null

Next we need to compare the files in the directory (temp table) to the records in the Document_Catalog.

WITH realfiles AS (
SELECT
  'DocName' = REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ),
  'CreateDate' = LEFT(Line,10)
FROM #DirContents
WHERE LEFT(Line,10) LIKE '12/%%/2009'
),
doccatfiles AS (
SELECT
EMP_ID
,REVERSE(LEFT(REVERSE(CAST(FILE_PATH AS VARCHAR(400))),CHARINDEX('\',REVERSE(CAST(FILE_PATH AS VARCHAR(400))))-1)) AS 'DocName'
FROM Document_Catalog
WHERE FILE_DATE >= '12/1/09'
AND DESC_DATE_2 >= '12/1/09'
)
SELECT DISTINCT doccatfiles.EMP_ID
FROM doccatfiles
WHERE doccatfiles.DocName NOT IN (SELECT realfiles.DocName FROM realfiles)

Note: Since we're doing so much text manipulation and comparing long strings of text, this query is rather slow. You'll see I limited the inputs here to only December 2009 to make the information more manageable. If you have a large issue, I would recommend separating it into batches like this.

You now have a list of invoices that are missing from your bill location. You can now recap these specific invoices rather than target all within a certain date range of the error.

Audit Modification Trails

With so many fields available in the client, matter and timekeep master files, changes are made to these records on a consistent basis. Often it is helpful to be able to see who changed what and when that change occurred. There are two tables within Elite that can help us in this search: the auditmod and auditmodd tables. The first contains the summary info for the changes and the second contains the details and descriptions. The code below will pull any changes to information in the matter master fields of matter contact, matter address and matter billing attorney that was performed yesterday by any user other than abc and def. Obviously, those can be tweaked to your individual needs. The code will return 2 lines of information for each change. The first will be the 'P' row which indicates the previous information. The second will be the 'N' row, which indicates the new information. This query also utilizes the Matter Description user defined function and the Timekeeper Full Name user defined function.

Code

SELECT 
audate AS 'Date'
,autime AS 'Time'
,auuser AS 'User'
,mmatter AS 'Matter #'
,clname1 AS 'Client Name'
,dbo.udfMattDesc(mmatter) AS 'Matter Description'
,dbo.udfTKFullName(mbillaty) AS 'Billing Aty'
,autype AS 'P/N'
,audesc AS 'Audit Description'
FROM auditmod
INNER JOIN auditmodd ON auditmod.auindex = auditmodd.auindex
INNER JOIN matter ON aukeyvalue1 = mmatter
INNER JOIN client ON mclient=clnum
WHERE autable = 'matter'
AND audate >= DATEADD(dd,0,DATEDIFF(dd,1,GETDATE()))
AND auuser NOT IN ('abc','def')
AND (
audesc LIKE 'mcontact%'
OR
audesc LIKE 'maddr%'
OR
audesc LIKE 'mbillaty%'
)
ORDER BY audate,aukeyvalue1,auditmodd.auindex

Results

Date	Time	User	Matter #	Client Name	Matter Description	Billing Aty	P/N	Audit Description
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	mbillaty 7381
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	mcontact
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	maddr1
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	maddr2
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	P	maddr3
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	mbillaty 1234
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	mcontact Jim McMahon
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	maddr1 Global Industries
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	maddr2 123 Wellington Court
2005-02-22 00:00:00.000	16:00:25	demo	3000-001	Global Industries	Global Industries - Audit	Stevens, Darren	N	maddr3 Lincolnshire, IL

Work in Process by Matter

The query below give you a list of matters with unbilled fees and costs. This query utilizes a feature in SQL Server 2005 called Common Table Expressions (or CTEs). You can use CTEs within the query rather than embedding multiple sub-queries. In this case, I use two CTEs to build a 'table' of unbilled time by matter and a 'table' of unbilled costs by matter. I then link those CTEs to the matter table using LEFT OUTER JOINs to make sure we get all the data. This also uses the Matter Description UDF.

WITH wiptime AS (
SELECT
tmatter,
SUM(tbillhrs) AS wiphrs,
SUM(tbilldol) AS wipfees
FROM timecard
WHERE tbilldt IS NULL
AND tinvoice IS NULL
GROUP BY tmatter),
wipcosts AS (SELECT
cmatter,
SUM(cbillamt) AS wipcosts
FROM cost
WHERE cbilldt IS NULL
AND cinvoice IS NULL
GROUP BY cmatter)
SELECT mmatter AS 'Matter #',
dbo.udfMattDesc(mmatter) AS 'Matter Description',
ISNULL(wiphrs,0) AS 'WIP Hours',
ISNULL(wipfees,0) AS 'WIP Fees',
ISNULL(wipcosts,0) AS 'WIP Costs',
ISNULL(wipfees,0)+ISNULL(wipcosts,0) AS 'Total WIP'
FROM matter
LEFT OUTER JOIN wiptime ON mmatter=tmatter
LEFT OUTER JOIN wipcosts ON mmatter=cmatter
WHERE mclient = '1000'
AND (ISNULL(wipfees,0)+ISNULL(wipcosts,0)) <> 0

Output Example

Matter #	Matter Description	WIP Hours	WIP Fees	WIP Costs	Total WIP
1000-001	Triad v. Magnuson	201.06	148033.265	2260.93	150294.195
1000-002	Triad v. Hanover Insurance	94.65	27254.01	901.70	28155.71
1000-003	Triad v. Keener Mfg.	0.00	0.00	1500.30	1500.30
1000-004	Triad v. Markham	49.59	12902.82	110.71	13013.53
1000-005	Triad Gadget Claims (Split Party #1)	28.05	8631.85	1000.00	9631.85
1000-006	Triad Gadget Claims (Split Party #2)	35.64	12453.15	1500.00	13953.15
1000-007	Triad Gadget Claims (Split Billing File)	8.40	2890.00	0.00	2890.00
1000-008	Triad v. Kilkenny Design	19.50	1680.02	2500.00	4180.02
1000-009	Practice Development - Triad	29.50	8418.75	6175.00	14593.75
1000-015	Tessting lee	0.00	0.00	100.00	100.00

Fee Applications by Credit Document

The following code will return how a specific document (or group of documents) have been applied from a fee perspective.

SELECT
linvoice AS 'Invoice #',
ldocumnt AS 'Payment #',
ltradat AS 'Payment Date',
llcode AS 'Ledger Code',
tkinit AS 'Tk #',
tkblast AS 'Tk Name',
sum(lfamnt) as 'Fee Amt'
from ledgfee
INNER JOIN ledger on ledgfee.lindex = ledger.lindex
INNER JOIN timekeep on lftk = tkinit
where 
linvoice = 11714
group by linvoice, ldocumnt, ltradat, llcode, tkinit, tkblast

Results

Invoice #	Payment #	Payment Date	Ledger Code	Tk #	Tk Name	Fee Amt
11714	3245	2001-04-06	PAY	1234	Stevens	1400
11714	3245	2001-04-06	PAY	6514	Wright	4400
11714	3245	2001-04-06	PAY	7381	Baker	3250
11714	3245	2001-04-06	PAY	8477	Williams	475

First and Last Day of Month User Defined Functions

I often find myself in a situation where I need to return the first or last day of the month in various queries. Despite this being a seemingly simple process (that I'm sure EVERYONE needs at some point), Microsoft's method of obtaining this data is pointlessly cumbersome. The following piece of code will return the last day of today's month

SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE())+1, 0))

To me, this is simply ridiculous. First of all, there's so many nested functions in this query that I can't keep them all straight. Next, there's NO way you can remember it all to plug in to a query at any given moment (was this a +1 or a -1 at this spot??). So, below is some code to create two user defined functions that will return either the first day of the month or the last day of the month of any given date passed to the function.

First Day of Month:

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfFirstDayOfMonth]
(
@startDate DATETIME
)
RETURNS DATETIME
AS
BEGIN

RETURN DATEADD(mm, DATEDIFF(mm,0,@startDate), 0)

END

Last Day of Month:

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfLastDayOfMonth]
(
@startDate DATETIME
)
RETURNS DATETIME
AS
BEGIN

RETURN DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@startDate)+1, 0))

END

To use the user defined functions, simply pass a date to the function as a variable in your SELECT statement.

SELECT dbo.udfFirstDayOfMonth(GETDATE()) --First date of this month
SELECT dbo.udfFirstDayOfMonth(GETDATE()-5) --First date of the month 5 days prior to today

This should make using beginning and ending dates of months a bit easier for you.

Proforma Checks

Each month I run the following two queries to double-check our proforma runs. The first is a count by batch with beginning and ending proforma numbers and the second is similar, but by timekeeper.

Number of Proformas by Batch

SELECT
phbatch AS 'Batch No.',
bop AS Operator,
min(phindex) AS 'First Proforma',
max(phindex) AS 'Last Proforma',
count(phindex) AS '# of Proformas'
FROM prohead
INNER JOIN batch ON phbatch = bbatch
WHERE bper = '1209'
GROUP BY phbatch,bop

Number of Proformas by Timekeeper

SELECT tkloc AS 'Location',
phbatch AS 'Batch No.',
tkinit AS 'Tk #',
tklast AS 'Last Name',
MIN(phindex) AS 'First Proforma',
MAX(phindex) AS 'Last Proforma',
COUNT(phindex) AS '# of Proformas'
FROM matter
INNER JOIN prohead ON phmatter = mmatter
INNER JOIN timekeep ON tkinit = mbillaty
INNER JOIN batch ON phbatch = bbatch
WHERE bper = '1209'
GROUP BY tkloc,phbatch,tkinit,tklast
ORDER BY phbatch,tkloc,tkinit