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)

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.

Matter List having Collections

The SQL code below will return a list of matters, with client name, city state and billing timekeeper for all matters of a given department that have had collections in 2008 or 2009.

SELECT
matter.mmatter AS 'Matter #',
client.clname1 AS 'Client Name',
dbo.udfMattDesc(matter.mmatter) AS 'Matter Description',
ISNULL(matter.mcontact,client.clcontact) AS 'Contact',
client.clcity AS 'City',
client.clstate AS 'State',
dbo.udfTKFullName(matter.mbillaty) AS 'Billing Attorney'
FROM
matter
INNER JOIN client ON matter.mclient=client.clnum
INNER JOIN timekeep ON matter.mbillaty=timekeep.tkinit
WHERE matter.mdept = 'D'
AND EXISTS
  (SELECT * FROM matths
  WHERE matter.mmatter=matths.mhmatter
  AND RIGHT(matths.mhper,2) IN ('08','09')
  AND matths.mhdocodc <> 0)
ORDER BY clname1,mmatter

As you can see, the above query utilizes the two user defined functions mentioned below.

Matter Description User Defined Function

In Enterprise, the matter description is split among three fields; mdesc1, mdesc2 and mdesc3. While the mdesc1 field is often enough, many times we need the entire matter description in a report. The following user defined function (udf) automates the combination of the three fields.

Code to create the udf:

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfMattDesc]
(
    @mmatter VARCHAR(15)
)
RETURNS VARCHAR(185)
AS
BEGIN
  DECLARE @mattdesc VARCHAR(185)

   SELECT @mattdesc = mdesc1 + ' ' + ISNULL(mdesc2,'') + ' ' + ISNULL(mdesc3,'')
   FROM matter
   WHERE matter.mmatter = @mmatter

   RETURN RTRIM(@mattdesc)

END

This creates a udf named udfMattDesc that requires a value (mmatter) be passed to it and will return the full matter description of the specified matter. See the following example query to use the udf.

SELECT
matter.mmatter,
client.clname1,
dbo.udfMattDesc(matter.mmatter)
FROM matter
INNER JOIN client ON matter.mclient=client.clnum
WHERE matter.mclient='123456'