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