Write-offs & Write-downs for Multi-Timekeepers

Here's a query that uses common table expressions to derive Accounts Receivable write-offs and Unbilled Time and Cost write-downs for multi-timekeepers and results in a single list of timekeepers with their volume break-outs. This is converted to FIRM currency, and is filtered for a single year and single title.

WITH AR AS (
SELECT
mtktk
,SUM(CASE WHEN ic.lcfco='F' THEN pmt.lamount/cdrate*mtkpercnt/100 ELSE 0 END) AS 'ARFees'
,SUM(CASE WHEN ic.lcfco='C' THEN pmt.lamount/cdrate*mtkpercnt/100 ELSE 0 END) AS 'ARCosts'
,SUM(CASE WHEN ic.lcfco NOT IN ('F','C') THEN pmt.lamount/cdrate*mtkpercnt/100 ELSE 0 END) AS 'AROther'
FROM ledger pmt
INNER JOIN ledcode pc ON pmt.llcode=pc.lccode
INNER JOIN ledger inv ON pmt.laptoin=inv.lindex
INNER JOIN ledcode ic ON inv.llcode=ic.lccode
INNER JOIN periodt ON pmt.lperiod=pe
INNER JOIN matter ON pmt.lmatter=mmatter
INNER JOIN currates ON mcurrency=curcode AND trtype='A' AND pebedt BETWEEN cddate1 AND cddate2
INNER JOIN mtkper ON mtkmatter=mmatter AND pebedt BETWEEN mtkdate1 AND mtkdate2 AND mtktype='SOURCE'
INNER JOIN timekeep ON mtktk=tkinit
WHERE tktitle='Member'
AND YEAR(pebedt)='2014'
AND pc.lcfco IS NULL
AND pc.lccollhs = 'N'
GROUP BY mtktk)
, WIP AS (
SELECT
mtktk
,SUM((mhdowkdb-mhdobidb)/cdrate*mtkpercnt/100) AS 'WIPFees'
,SUM(-mhwocdw*mtkpercnt/100) AS 'WIPCosts'
FROM
matths
INNER JOIN matter ON mhmatter=mmatter
INNER JOIN periodt ON mhper=pe
INNER JOIN currates ON mcurrency=curcode AND trtype='A' AND pebedt BETWEEN cddate1 AND cddate2
INNER JOIN mtkper ON mtkmatter=mmatter AND pebedt BETWEEN mtkdate1 AND mtkdate2 AND mtktype='SOURCE'
INNER JOIN timekeep ON mtktk=tkinit
WHERE tktitle='Member'
AND YEAR(pebedt)='2014'
AND mffee IS NULL
GROUP BY mtktk)
SELECT
tkinit AS 'TK #'
,tklast+', '+tkfirst AS 'Timekeeper'
,ISNULL(ARFees,0) AS 'AR Fees'
,ISNULL(ARCosts,0) AS 'AR Costs'
,ISNULL(AROther,0) AS 'AR Other'
,ISNULL(ARFees,0)+ISNULL(ARCosts,0)+ISNULL(AROther,0) AS 'AR Total'
,ISNULL(WIPFees,0) AS 'WIP Fees'
,ISNULL(WIPCosts,0) AS 'WIP Costs'
,ISNULL(WIPFees,0)+ISNULL(WIPCosts,0) AS 'WIP Total'
,ISNULL(ARFees,0) + ISNULL(ARCosts,0) + ISNULL(AROther,0) + ISNULL(WIPFees,0) + ISNULL(WIPCosts,0) AS 'Total'
FROM timekeep
LEFT OUTER JOIN AR ON tkinit=AR.mtktk
LEFT OUTER JOIN WIP ON tkinit=WIP.mtktk
WHERE tktmdate IS NULL
AND tktitle='Member'
ORDER BY [Timekeeper]

Client and Matter Exception Rates

Client and matter exception rates are one of the more difficult areas to manage in Enterprise. This can be complicated by the fact in versions prior to 3.10, there is no end date on the records. In order to end an exception, you had to enter a new line with a zero percent or dollar deviation. Thankfully this has been corrected in 3.10. For those still on prior versions, this bit of code gives you a full view of exceptions for clients and matters. In an effort to make the data a bit more manageable, there is a department variable that limits the results of the query to a single department.

This is probably as good a time as any to point out the merits (and limitations) of common table expressions (CTE) as opposed to sub-queries. You can see in the top portion of the query below that there is a "WITH" expression. Using a WITH expression creates a common table expression from a query and gives it a name, in this case maxmex. You can then call this CTE later in your query as if it were a table. However, note in the client section that I've used subqueries instead of a CTE. WITH expressions can only be used at the beginning of a query, and a previous query/statement must be ended with a semicolon before a WITH expression can be used. Since the UNION ALL is a continuation of the query, we can't use a WITH at that point. However, in my opinion, CTEs are much easier to read and understand than sub-queries, so I highly recommend their use.

DECLARE @Dept VARCHAR(2);
SET @Dept = '15';


WITH maxmex AS
 (
 SELECT mrmatter
 ,mrtk
 ,mrtitle
 ,MAX(mreffdate) as 'maxdate'
 FROM mexrate
 WHERE mrccode ='!'
 GROUP BY mrmatter,mrtk,mrtitle
 ),
rates AS
 (
 SELECT b.tkinit AS tkrinit
 ,b.tkeffdate AS tkrdate1
 ,MIN(CASE WHEN e.tkeffdate IS NULL THEN '1/1/2026' ELSE e.tkeffdate END)-1 AS tkrdate2
 ,b.tkrt01 AS tkrt01
 ,b.tkrtcur AS tkrtcur
 FROM timerate b
 LEFT OUTER JOIN timerate e ON b.tkinit=e.tkinit AND e.tkeffdate > b.tkeffdate AND b.tkrtcur=e.tkrtcur
 GROUP BY b.tkinit,b.tkeffdate,b.tkrt01,b.tkrtcur
 )
SELECT 
client.clnum AS 'Client #'
,client.clname1 AS 'Client Name'
,mexrate.mrmatter AS 'Matter #'
, matter.mdesc1 AS 'Matter Desc'
, deptlab.head1 AS 'Matter Dept'
, CASE WHEN mexrate.mrtitle='!' THEN mexrate.mrtk ELSE mexrate.mrtitle END AS 'Exception'
, CASE WHEN mexrate.mrtitle='!' THEN tk2.tklast+', '+tk2.tkfirst ELSE 'ALL' END AS 'Exception Name'
, CASE WHEN mexrate.mrtitle='!' THEN tk2.tktitle ELSE mexrate.mrtitle END AS 'Exception Title'
, CASE WHEN mexrate.mrtitle='!' THEN dept2.head1 ELSE 'ALL' END AS 'Exception Dept'
, CASE WHEN mexrate.mrtitle='!' THEN ldesc ELSE 'ALL' END AS 'Exception Loc'
, mexrate.mreffdate
, mexrate.mrrate
, mexrate.mrdevper
, mexrate.mrdevdol
, mexrate.mrrtnum
, rates.tkrt01
, rates.tkrtcur
--, matter.mbillaty
--, deptlab_billatty.head1
--, matter.mclosedt
FROM   mexrate mexrate 
INNER JOIN matter matter  ON mexrate.mrmatter=matter.mmatter
INNER JOIN deptlab deptlab ON matter.mdept=deptlab.delcode
INNER JOIN maxmex maxmex ON mexrate.mrmatter=maxmex.mrmatter AND mexrate.mrtk=maxmex.mrtk AND mexrate.mrtitle=maxmex.mrtitle AND mexrate.mreffdate=maxmex.maxdate
INNER JOIN timekeep timekeep ON matter.mbillaty=timekeep.tkinit
LEFT OUTER JOIN timekeep tk2 ON maxmex.mrtk=tk2.tkinit
LEFT OUTER JOIN deptlab dept2 ON tk2.tkdept=dept2.delcode
LEFT OUTER JOIN location ON tk2.tkloc=locode
INNER JOIN deptlab deptlab_billatty ON timekeep.tkdept=deptlab_billatty.delcode
INNER JOIN client ON mclient=clnum
LEFT OUTER JOIN rates ON mexrate.mrtk=rates.tkrinit AND rates.tkrdate2='12/31/2025'
WHERE  matter.mclosedt IS NULL 
AND (mexrate.mrtk='!' OR mexrate.mrtk IN (SELECT tkinit FROM timekeep WHERE tktmdate IS NULL)) 
AND (mrdevper<>0 OR mrdevper IS NULL)
AND (mrdevdol<>0 OR mrdevdol IS NULL)
AND matter.mdept=@Dept

UNION ALL

-------------------------------------------------------------------------------------------
-- Client Exceptions
-------------------------------------------------------------------------------------------

SELECT cexrate.crclient AS 'Client #'
, client.clname1 AS 'Client Name'
, 'All' AS 'Matter #'
, 'All' AS 'Matter Desc'
, 'All' AS 'Matter Dept'
, CASE WHEN cexrate.crtitle='!' THEN cexrate.crtk ELSE cexrate.crtitle END AS 'Exception'
, CASE WHEN cexrate.crtitle='!' THEN tk2.tklast+', '+tk2.tkfirst ELSE 'ALL' END AS 'Exception Name'
, CASE WHEN cexrate.crtitle='!' THEN tk2.tktitle ELSE cexrate.crtitle END AS 'Exception Title'
, CASE WHEN cexrate.crtitle='!' THEN dept2.head1 ELSE 'ALL' END AS 'Exception Dept'
, CASE WHEN cexrate.crtitle='!' THEN ldesc ELSE 'ALL' END AS 'Exception Loc'
, cexrate.creffdate
, cexrate.crrate
, cexrate.crdevper
, cexrate.crdevdol
, cexrate.crrtnum
, rates.tkrt01
, rates.tkrtcur
FROM   cexrate 
INNER JOIN 
 (SELECT crclient
 ,crtk
 ,crtitle
 ,MAX(creffdate) as 'maxdate'
 FROM cexrate
 WHERE crccode ='!'
 GROUP BY crclient,crtk,crtitle) AS
maxcex ON cexrate.crclient=maxcex.crclient AND cexrate.crtk=maxcex.crtk AND cexrate.crtitle=maxcex.crtitle AND cexrate.creffdate=maxcex.maxdate
LEFT OUTER JOIN timekeep tk2 ON maxcex.crtk=tk2.tkinit
LEFT OUTER JOIN deptlab dept2 ON tk2.tkdept=dept2.delcode
LEFT OUTER JOIN location ON tk2.tkloc=locode
INNER JOIN client ON cexrate.crclient=client.clnum
LEFT OUTER JOIN
 (
 SELECT b.tkinit AS tkrinit
 ,b.tkeffdate AS tkrdate1
 ,MIN(CASE WHEN e.tkeffdate IS NULL THEN '1/1/2026' ELSE e.tkeffdate END)-1 AS tkrdate2
 ,b.tkrt01 AS tkrt01
 ,b.tkrtcur AS tkrtcur
 FROM timerate b
 LEFT OUTER JOIN timerate e ON b.tkinit=e.tkinit AND e.tkeffdate > b.tkeffdate AND b.tkrtcur=e.tkrtcur
 GROUP BY b.tkinit,b.tkeffdate,b.tkrt01,b.tkrtcur
 ) AS rates ON cexrate.crtk=rates.tkrinit AND rates.tkrdate2='12/31/2025'

WHERE  (cexrate.crtk='!' OR cexrate.crtk IN (SELECT tkinit FROM timekeep WHERE tktmdate IS NULL)) 
AND (crdevper IS NULL OR crdevper <> 0)
AND (crdevdol IS NULL OR crdevdol <> 0)
AND EXISTS (SELECT * FROM matter WHERE mclient=clnum AND mdept=@Dept AND mclosedt IS NULL)

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