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.