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

Which Tables are Fueling Your DB Growth?

Here's a link to a wonderful script from Mitchel Sellers that creates a stored procedure that, when executed, returns a list of all the tables in your database and the size of each table.

Here's what the output looks like.

tableName	numberofRows	reservedSize	dataSize	indexSize	unusedSize
authors	227	32 KB	24 KB	8 KB	0 KB
books	822	112 KB	40 KB	72 KB	0 KB

The only drawback to the output is that you can't sort by size as it's a text field.

UPDATE:
Ok, so the drawback has been bugging me for a while, so I decided to eliminate it. Replace all code in the stored procedure above AFTER 'DEALLOCATE tableCursor' with the following:

--Another procedure level temp table to store the results minus the 'kb'
CREATE TABLE #TempTable2
(
tableName varchar(100),
numberofRows int,
reservedSize int,
dataSize int,
indexSize int,
unusedSize int
)

INSERT INTO #TempTable2
SELECT tableName,
CAST(numberofRows AS int),
CAST(LEFT(reservedSize, LEN(reservedSize)-3) AS int),
CAST(LEFT(dataSize, LEN(dataSize)-3) AS int),
CAST(LEFT(indexSize, LEN(indexSize)-3) AS int),
CAST(LEFT(unusedSize, LEN(unusedSize)-3) AS int)
FROM #TempTable

--Select all records so we can use the reults
SELECT * 
FROM #TempTable2
ORDER BY reservedSize DESC

--Final cleanup!
DROP TABLE #TempTable
DROP TABLE #TempTable2

Undocumented Stored Procedures

Here's a link to an excellent article at SQLServerCentral.com on undocumented stored procedures in SQL Server 2005. Free registration is required (and well worth it).

Minimum and Maximum Rate by Timekeeper Title

This fairly simple script will find the current minimum and maximum rate per title for non-terminated timekeepers whose time entry flag is checked. This is based on rates in the timerate table versus the accounting rate table.

SELECT tktitle AS 'Title',
MIN(tkrt01) AS 'Minimum Rate',
MAX(tkrt01) AS 'Maximum Rate'
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
GROUP BY tktitle

Working Timekeeper Collections for a Specific Billing Timekeeper

This query will return a summary of collections by working timekeeper for a specific billing timekeepers historical matters. This will give you insights as to who exactly is working on the matters of a specific billing attorney. Included in this query is also an example of how to use the OVER() function available to those on SQL Server 2005 and 2008. The OVER() function allows you to sum the contents of a column to facilitate, among other things, creating a percent-to-total value for each row without an additional call to the database. This query also uses the timekeeper name user defined function.

The core of this query was submitted by Penny Turner, IS Director at Smith Haughey Rice & Roegge and was edited to include the percent to total functionality and grand total. Many thanks to Penny!

SELECT mttk AS 'TK #',
dbo.udfTKFullName(mttk) AS 'Working TK',
Collections,
CAST(1.00*Collections/SUM(Collections) OVER() AS DECIMAL(16,4)) AS 'Pct to Total'
FROM
(
SELECT mttk,
tklast,
SUM(mtdocodc) AS 'Collections'
FROM mattimhs
INNER JOIN matter ON mtmatter=mmatter
INNER JOIN hmatter ON hmatter=mmatter AND mbillaty=hmbaty
INNER JOIN timekeep ON tkinit=mttk
INNER JOIN periodt ON mtper=pe
WHERE YEAR(peendt) = '2009'
AND hmbaty ='2100'
AND pebedt BETWEEN hmdate1 AND hmdate2
AND mtdocodc<>0
GROUP BY mttk,tklast
) AS a
UNION
SELECT 'GT','Grand Total',SUM(mtdocodc),NULL
FROM mattimhs
INNER JOIN matter ON mtmatter=mmatter
INNER JOIN hmatter ON hmatter=mmatter AND mbillaty=hmbaty
INNER JOIN timekeep ON tkinit=mttk
INNER JOIN periodt ON mtper=pe
WHERE YEAR(peendt) = '2009'
AND hmbaty ='2100'
AND pebedt BETWEEN hmdate1 AND hmdate2
AND mtdocodc<>0

Current Proformas

Occasionally I will find myself in need of a list of current proformas pertaining to a specific client or timekeeper. The following code will pull a list of current proformas along with the number and amount of fee entries and number and amount of cost entries. I've used a beginning date in the query that is rather arbitrary, however, some 'recent' date was needed to remove anomalies from conversions long ago. This query uses the Matter Description user defined function.

SELECT
mmatter AS 'Matter #',
clname1 AS 'Client Name',
dbo.udfMattDesc(mmatter) AS 'Matter Description',
prohead.phindex AS 'Proforma #',
phdate AS 'Date',
dbo.udfTKFullName(mbillaty) AS 'Billing Aty',
COUNT(timecard.tindex) AS '# Timecards',
ISNULL(SUM(timecard.tbilldol),0) AS 'Fee Amount',
COUNT(cost.cindex) AS '# Costcards',
ISNULL(SUM(cost.cbillamt),0) AS 'Cost Amount'
FROM prohead
INNER JOIN matter ON mmatter=phmatter
INNER JOIN client ON mclient=clnum
INNER JOIN timekeep ON mbillaty=tkinit
INNER JOIN prodetail ON prohead.phindex=prodetail.phindex
LEFT OUTER JOIN cost ON prodetail.pddetail=cost.cindex AND pdtype='C' AND cinvoice IS NULL
LEFT OUTER JOIN timecard ON prodetail.pddetail=timecard.tindex AND pdtype='T' AND tinvoice IS NULL
WHERE
phdate >= '1/1/09'
AND phstatus LIKE 'C%'
GROUP BY mmatter,
clname1,
mbillaty,
prohead.phindex,
phdate
HAVING COUNT(timecard.tindex)>0 OR COUNT(cost.cindex)>0
ORDER BY prohead.phindex

Volumes - Vouchers

The following is a simple query to assess the volume of vouchers being processed each month. This includes all voucher types.

SELECT
YEAR(votrdt) AS 'Year',
MONTH(votrdt) AS 'Month',
COUNT(vo_id) AS '# Vouchers',
SUM(voamt) AS 'Voucher Amt'
FROM apvo
WHERE
YEAR(votrdt) >= '2005'
GROUP BY YEAR(votrdt),MONTH(votrdt)
ORDER BY YEAR(votrdt),MONTH(votrdt)

Volumes - Credits

This will be the first in a series of queries on volumes of transactions. Understanding the volume of transactions flowing through the system is an important aspect of managing the accounting department. The following measures the number of documents and dollar value of non-reversed payments received by period.

SELECT
YEAR(peendt) AS 'Year',
MONTH(peendt) AS 'Month',
SUM(numdocs) AS '# Documents',
SUM(sumpmt) AS '$ Payments'
FROM
(SELECT
lbatch,
lperiod,
llcode,
peendt,
COUNT(DISTINCT ldocumnt) AS numdocs,
SUM(lamount) AS sumpmt
FROM
ledger
INNER JOIN periodt ON lperiod=pe
INNER JOIN ledcode ON llcode=lccode
WHERE lccollhs='Y'
AND peendt >= '1/1/05'
AND lzero <> 'R'
GROUP BY lbatch,lperiod,llcode,peendt) AS batchledg
GROUP BY YEAR(peendt),MONTH(peendt)
ORDER BY YEAR(peendt),MONTH(peendt)

Collections by Worked Period for Specific Collections Month

Collections is an important aspect of any law firm's monthly stats. Delving a little deeper into the collections number, though, can yield some interesting information. The following query will tell you which worked period your collections have come from. For example, let's say in September you collected $5 million dollars. Of that $5 million, 3 million was worked in August and 1 million was worked in July and the other million was worked prior to July. You could now compare this to prior months or prior years to see if your worked period comparisons are staying consistent.

SELECT
peendt,
SUM(lfamnt) AS 'Fee Collections'
FROM ledger l1
INNER JOIN ledgfee l2 ON l1.lindex=l2.lindex
INNER JOIN periodt p1 ON l2.lwoper=p1.pe
INNER JOIN ledcode ON llcode=lccode
WHERE l1.lperiod = '0909' --Collections Period
AND lccollhs = 'Y'
GROUP BY peendt
ORDER BY peendt DESC

Timekeepers Who've Worked on a Client

The following will return a list of timekeepers that have worked on a particular client for 2009 with the total hours worked. This is sorted by title and then by hours worked descending.

SELECT
tkinit AS 'TK #',
dbo.udfTKFullName(tkinit) AS Timekeeper,
tktitle AS Title,
tkstrate AS 'Standard Rate',
SUM(mthrwkdw) AS 'Hours Worked'
FROM
timekeep
INNER JOIN mattimhs ON mttk=tkinit
INNER JOIN matter ON mtmatter=mmatter
INNER JOIN periodt ON mtper=pe
WHERE mclient='112233'
AND peendt >= '1/1/09'
GROUP BY tktitle,tkstrate,tkinit
ORDER BY tktitle,SUM(mthrwkdw) DESC

Year-to-Date Billed Fees and Cost with Current Work in Process

Elite provides a host of pre-written reports in it's Reports & Queues module, but most never seem to get at exactly what you need. And then when they do, the client names and matter descriptions are cut off at 20 characters (or some other insane #). The below query will give you year-to-date billed fees and costs as well as the current work in process values by matter for a specific client.

SELECT
mmatter AS 'Matter #',
dbo.udfMattDesc(mmatter) AS 'Matter Description',
ISNULL(t.feewip,0) AS 'Fee WIP',
ISNULL(c.wipcost,0) AS 'Cost WIP',
ISNULL(t.feewip,0)+ISNULL(c.wipcost,0) AS 'Total WIP',
ISNULL(t.billamt,0) AS 'Fees Billed YTD',
ISNULL(c.cbillamt,0) AS 'Costs Billed YTD',
ISNULL(t.billamt,0)+ISNULL(c.cbillamt,0) AS 'Total Billed'
FROM matter
LEFT OUTER JOIN
(
 SELECT tmatter,
 SUM(CASE WHEN tbilldt IS NULL THEN tbilldol ELSE 0 END) AS feewip,
 SUM(CASE WHEN RIGHT(tbiper,2)='09' THEN tbilldol ELSE 0 END) AS billamt
 FROM timecard
 GROUP BY tmatter
) AS t ON mmatter=t.tmatter
LEFT OUTER JOIN
(
 SELECT cmatter,
 SUM(CASE WHEN cbilldt IS NULL THEN cbillamt ELSE 0 END) AS wipcost,
 SUM(CASE WHEN RIGHT(cbiper,2)='09' THEN cbillamt ELSE 0 END) AS cbillamt
 FROM cost
 GROUP BY cmatter
) AS c ON mmatter = c.cmatter
WHERE mclient='998877'

Conflicts Search Options

One thing about the search options in Conflicts Prospective Search and Quick Search is that the user can modify settings as to which fields in the database are being searched. This can lead to different users performing the same search, but getting different results. To check your users settings here, you can use the following code:

select * from vboptions where vboname = 'CnfSearchCustom_UserFields'  -- Quick Search settings
select * from vboptions where vboname = 'CnfSearchPro_UserFields'  -- Prospective settings

If you notice many differences, especially among a group of people that should have the same settings, you can verify the 'correctness' of an individual and copy those settings to other users without them having to print out the options screen and go item by item. The following code will suffice:

--View Options for user with 'correct' settings.

select vbovalue from vboptions where vboname = 'CnfSearchCustom_UserFields' and vbouser = 'xyz'

--Copy Results from above to the long value below
begin transaction conflictsupdate
update vboptions
set vboptions.vbovalue = 'YYYYYNNNNNNNNYYYNNNNNNYNNNYYNNNNNNNN'
where vboname = 'CnfSearchCustom_UserFields'
and vbouser in ('jkl','ghi','def','abc')
commit transaction conflictsupdate

begin transaction conflictsupdate
update vboptions
set vboptions.vbovalue = 'YYYYYNNNNNNNNYYYNNNNNNYNNNYYNNNNNNNN'
where vboname = 'CnfSearchPro_UserFields'
and vbouser in ('jkl','ghi','def','abc')
commit transaction conflictsupdate

It goes without saying that when you are performing an update on Elite, test liberally on a test system first, and apply update to production system after hours.

Checks with Descriptions for Specific Bank Account

The following will provide a list of checks disbursed on a specific bank account. You can add date qualifications or any other criteria as well. It will return a line for each 'check line', so check numbers and amounts will repeat for checks with multiple lines. This was originally developed for an Extend event, but we ended up going a different direction. This uses the check line description user defined function.

SELECT
csd.cknum,
csd.ckdate,
csd.apnum,
csd.ckamt,
csd.appynm,
csddt.ckline,
csddt.vo_id,
csddt.glnum,
csddt.glrc1,
csddt.amt,
dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS ckdes,
apvo.voinv,
apvo.voindt,
'********'+RIGHT(ap.apaccount,4) AS acctno
FROM
csd
INNER JOIN csddt ON csd.cknum=csddt.cknum AND csd.baid=csddt.baid
INNER JOIN apvo ON csddt.vo_id = apvo.vo_id
INNER JOIN ap ON csd.apnum=ap.apnum
WHERE csd.baid = 'BANKIACH'
AND voiddt IS NULL

Check Line Description User Defined Function

Ok, by now you've probably figured out that I'm a huge fan of user defined functions. Well, I am. Just this past week I had the need for yet another concatenation of rows thanks to Elite's bizarre handling of description fields. This one relates to check descriptions. Each GL line of a check has a description attached to it that can span multiple lines in the csddes table. So, the following udf will combine all the description lines for each bank / check / check line combination.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfCsdDes]
(
@baid VARCHAR(8),
@cknum VARCHAR(8),
@ckline INT
)
RETURNS VARCHAR(4800) AS
BEGIN
 DECLARE @description VARCHAR(4800)

 SELECT @description = ''

 SELECT @description = @description + ' ' + des FROM csddes WHERE baid = @baid AND cknum = @cknum AND ckline = @ckline ORDER BY dsline

 RETURN LTRIM(RTRIM(@description))
END

You can call this udf in any query by passing the bankid, check number and check line variable, typically from the csddt table. Example will follow in the next post.

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.