Current Day Ledger Write Offs

The code below will give you current day ledger write offs by matter. The matter description udf and timekeeper name udf are used here. This is based on ledger code = 'WOFF'.

SELECT l.lindex AS 'Ledger Index',
l.lmatter AS 'Matter #',
clname1 AS 'Client Name',
dbo.udfMattDesc(l.lmatter) AS 'Matter Description',
mbillaty AS 'Bill Aty #',
dbo.udfTKFullName(mbillaty) AS 'Billing Attorney',
l.ldocumnt AS 'Document #',
l.ltradat AS 'Transaction Date',
l.llcode AS 'Ledger Code',
l1.lddesc AS 'Ledger Description',
sum(l.lamount) AS 'Amt Written Off'
FROM ledger l
INNER JOIN matter ON l.lmatter = mmatter
INNER JOIN client ON mclient = clnum
INNER JOIN timekeep ON mbillaty = tkinit
LEFT OUTER JOIN ledgdesc l1 ON l.lindex = l1.lindex AND ldline = 1
WHERE l.llcode in ('WOFF')
AND ltradat > getdate()-2
AND lzero <> 'R'
GROUP BY l.lindex, l.lmatter, clname1,
mbillaty, l.ldocumnt,
l.ltradat, l.llcode, l1.lddesc
ORDER BY l.lmatter ASC

Maximum and Minimum Rate per Timekeeper Title

This query will return by timekeeper title the highest and lowest current rate found in the timerate table. This doesn't take into account the accounting rate table.

SELECT tktitle, MIN(tkrt01) AS minrate, MAX(tkrt01) AS maxrate
FROM timekeep
INNER JOIN (
  SELECT tkinit, MAX(tkeffdate) AS tkeffdate
  FROM timerate
  GROUP BY tkinit) AS recentrate ON recentrate.tkinit=timekeep.tkinit
INNER JOIN timerate ON recentrate.tkinit=timerate.tkinit AND recentrate.tkeffdate=timerate.tkeffdate
WHERE tkeflag = 'Y'
AND tktmdate IS NULL
AND tksect = 'Team01'
GROUP BY tktitle

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.

Timekeeper Name User Defined Function

This is similar in nature to the matter description udf described in the last post. The timekeeper name udf will return the last name, first name of the tkinit that is passed in the function.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfTKFullName]
(
@tkinit VARCHAR(5)
)
RETURNS VARCHAR(85)
AS
BEGIN

DECLARE @fullname VARCHAR(85)

SELECT @fullname = tklast + (CASE WHEN tkfirst IS NULL THEN '' ELSE ', ' END) + ISNULL(tkfirst,'')
FROM timekeep
WHERE tkinit = @tkinit

RETURN @fullname

END

To use the user defined function, simply invoke it in the SELECT statement and pass it a value to execute on.

SELECT dbo.udfTKFullName(tkinit)
FROM timekeep
WHERE tkinit = '1234'

Using a UDF here will help you avoid having to CONCATENATE or COALESCE the parts of the name in each query.

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'

Vendors with No Activity

It is often a good idea to sift through records to determine active records that have had no activity to see if they need to be made inactive. Below is a query to identify active vendor records that have had no activity.

SELECT
ap.apnum,
ap.apname,
ap.*
FROM ap
WHERE ap.lckdate IS NULL
AND NOT EXISTS (SELECT * FROM apamt WHERE ap.apnum=apamt.apnum)
AND NOT EXISTS (SELECT * FROM apvo WHERE ap.apnum=apvo.apnum)
AND NOT EXISTS (SELECT * FROM trcheck WHERE ap.apnum=trcheck.trvendor)
AND NOT EXISTS (SELECT * FROM csd WHERE ap.apnum=csd.apnum)
AND NOT EXISTS (SELECT * FROM rapvo WHERE ap.apnum=rapvo.apnum)

While the ap.lckdate (last check date) is an obvious criteria here, vendors can have activity other than checks that needs to be searched for. Therefore, we include the voucher table, repeating voucher table, vendor summary totals, trust check table and the check detail table. If the vendor doesn't appear in any of these, then it's definitely an inactive vendor.

Beginnings

The starting point for this blog will be the presentation I gave in June 2009 for the International Legal Technology Association, otherwise known as ILTA. This webinar included the basics of SQL and how it relates to the Elite Enterprise environment.