XML Superbill Time Description Enhancements

Another issue that we came across with the XML Superbill was that the presense of a caret in the narrative did not function the same as in the old superbill. If you were not aware, if there is a caret "^" in the narrative, anything after the caret does not output on a normal superbill, and therefore doesn't show on the invoice. The XML Superbill code did not respect the caret, and includes the entire narrative. A quick change to the SQL in the stored procedure (dg_xml_timedesc) that delivers the narrative to the XML Superbill did the trick.

Old Code:

set @mysql =
 N'set @mystrmax =
   (select tdline, tddesc from ' + @timedesc + N' timedesc
   where tindex = @tindex
   and tddesc is not null
   order by tdline for xml raw)'

New Code:

set @mysql =
 N'set @mystrmax =
   (select tdline, LEFT(tddesc,CASE WHEN CHARINDEX(''^'',tddesc)=0 THEN LEN(tddesc) ELSE CHARINDEX(''^'',tddesc)-1 END) as tddesc
   from ' + @timedesc + N' timedesc
   where tindex = @tindex
   and tddesc is not null
   AND NOT EXISTS (SELECT * FROM timedesc caret WHERE timedesc.tindex=caret.tindex AND CHARINDEX(''^'',caret.tddesc)>0 AND timedesc.tdline>caret.tdline)
   order by tdline for xml raw)'

XML Superbill Timecard Changes

We recently began using Design Gallery for our invoice output, replacing the aging Document Studio process. As part of this, we deployed the newish XML Superbill developed by Elite. We have found that it largely meets our needs, though a few pieces of information are missing that we needed to have. Thankfully, much of the XML Superbill is driven by stored procedures in SQL, which makes customizing the XML Superbill a fairly straight-forward process. While for some items, it's easy to write a pre-process in Design Gallery, this adds additional overhead to the SQL Server as additional queries are being executed against the database. If you can customize the stored procedure, and pull in the data you need in an existing query, it's much more advantageous to do so. However, make sure you document all your changes well so future updates to the XML Superbill do not leave you in a bind to recreate all your hard work. The first customization I'll show is to the timecard stored procedure: dg_xml_timecards.

Old Code: Starting around line 210, this sql string forms the backbone of the query to pull all the timecard details that get inserted into the XML output.

set @mysql =
 N'set @mystrmax = (
 select timecard.*, timekeep.tkinit, tkfirst, tklast, tkinitb, tkbfirst, tkblast, tktitle, tksort,
  tkloc, location.ldesc,
  temptaxdetail.tax,  temptaxdetail.tccode,  temptaxdetail.tcdesc,  temptaxdetail.tcrate,
  temprates.tkrt01 timerate1,
  convert(decimal(16,2), temprates.tkrt01 * timecard.tbillhrs) as timevalue1,
  temprates.tkrt02 timerate2,
  convert(decimal(16,2), temprates.tkrt02 * timecard.tbillhrs) as timevalue2,
  temptk.astd, 
  convert(decimal(16,2), temptk.astd * timecard.tbillhrs) as astdvalue,
  title.tisort,
  actcode.adesc1, actcode.adesc2, actcode.adesc3, actcode.adesc4,
  actcode.adesc5, actcode.adesc6, actcode.adesc7, actcode.adesc8,
  actcode.adesc9, actcode.adesc10, actcode.actname,
  @task_code task_code, @task_description task_description,
  @activity_code activity_code, @activity_description activity_description,
  @cttitl1 cttitl1,
  @cttitl2 cttitl2,
  @cttitl3 cttitl3,
  @cttitl4 cttitl4,
  @cttitl5 cttitl5
 from ' + @timecard + N' timecard
 join ' + @temptaxdetail  + N' temptaxdetail
 on temptaxdetail.source = ''' + @timecard + N'''
 and temptaxdetail.source_index = timecard.tindex 
 join ' + @temprates + N' temprates
 on temprates.source = ''' + @timecard + N'''
 and temprates.source_index = timecard.tindex  
 join timekeep on timecard.ttk = timekeep.tkinit
 join location on timekeep.tkloc = location.locode
 join title on timekeep.tktitle = title.tititle   
 join ' + @temptk + N' temptk
 on temptk.ratenumber = 1 and timecard.ttk = temptk.tkinit and timecard.tmatter = temptk.mmatter
 left join actcode on timecard.tcode = actcode.acode
 where timecard.tindex = @tindex
 for xml raw)'

New Code: We wanted to include the original value and hours for split timecards and a flag to indicate whether the timecard was a direct charge, or a split charge from a master matter. You can see we pull in all fields from the sptime table, and the CASE statement following that is the indicator flag. The tables are joined at the bottom in a LEFT OUTER JOIN.

set @mysql =
 N'set @mystrmax = (
 select timecard.*, timekeep.tkinit, tkfirst, tklast, tkinitb, tkbfirst, tkblast, tktitle, tksort,
  tkloc, location.ldesc,
  temptaxdetail.tax,  temptaxdetail.tccode,  temptaxdetail.tcdesc,  temptaxdetail.tcrate,
  temprates.tkrt01 timerate1,
  convert(decimal(16,2), temprates.tkrt01 * timecard.tbillhrs) as timevalue1,
  temprates.tkrt02 timerate2,
  convert(decimal(16,2), temprates.tkrt02 * timecard.tbillhrs) as timevalue2,
  temptk.astd, 
  convert(decimal(16,2), temptk.astd * timecard.tbillhrs) as astdvalue,
  title.tisort,
  actcode.adesc1, actcode.adesc2, actcode.adesc3, actcode.adesc4,
  actcode.adesc5, actcode.adesc6, actcode.adesc7, actcode.adesc8,
  actcode.adesc9, actcode.adesc10, actcode.actname,
  @task_code task_code, @task_description task_description,
  @activity_code activity_code, @activity_description activity_description,
  @cttitl1 cttitl1,
  @cttitl2 cttitl2,
  @cttitl3 cttitl3,
  @cttitl4 cttitl4,
  @cttitl5 cttitl5,
  sptime.*,
  CASE WHEN sptime.sptindex IS NOT NULL THEN ''S'' ELSE ''D'' END AS split_direct
 from ' + @timecard + N' timecard
 join ' + @temptaxdetail  + N' temptaxdetail
 on temptaxdetail.source = ''' + @timecard + N'''
 and temptaxdetail.source_index = timecard.tindex 
 join ' + @temprates + N' temprates
 on temprates.source = ''' + @timecard + N'''
 and temprates.source_index = timecard.tindex  
 join timekeep on timecard.ttk = timekeep.tkinit
 join location on timekeep.tkloc = location.locode
 join title on timekeep.tktitle = title.tititle   
 join ' + @temptk + N' temptk
 on temptk.ratenumber = 1 and timecard.ttk = temptk.tkinit and timecard.tmatter = temptk.mmatter
 left join actcode on timecard.tcode = actcode.acode
 LEFT OUTER JOIN sptimes ON timecard.tindex = sptimes.sptsindex
 LEFT OUTER JOIN sptime ON sptimes.sptindex = sptime.sptindex
 where timecard.tindex = @tindex
 for xml raw)'

Shortly thereafter is another query string that actually defines the XML output. You will need to add your new new nodes into this output string. Notice the new card-value type "split-original" with the new nodes inside, as well as the indicator flag "split-direct".

set @mystrmax = convert(nvarchar(max), @x.query('
for $row in row return
<root>
<timekeeper>
<initials>{data($row/@tkinit)}</initials>
<firstname>{data($row/@tkfirst)}</firstname>
<lastname>{data($row/@tklast)}</lastname>
<billing-initials>{data($row/@tkinitb)}</billing-initials>
<billing-lastname>{data($row/@tkblast)}</billing-lastname>
<billing-firstname>{data($row/@tkbfirst)}</billing-firstname>
{if (string-length(data($row/@tkblast)) gt 0) then
 if (string-length(data($row/@tkbfirst)) gt 0) then
  (<billingname>{concat(data($row/@tkbfirst)," ",data($row/@tkblast))}</billingname>,
  <shortname>{concat(substring(data($row/@tkbfirst),1,1)," ",data($row/@tkblast))}</shortname>)
 else
  (<billingname>{data($row/@tkblast)}</billingname>,
  <shortname>{data($row/@tkblast)}</shortname>)
else
 if (string-length(data($row/@tkfirst)) gt 0) then
   (<billingname>{concat(data($row/@tkfirst)," ",data($row/@tklast))}</billingname>,
    <shortname>{concat(substring(data($row/@tkfirst),1,1)," ",data($row/@tklast))}</shortname>)
 else
   (<billingname>{data($row/@tklast)}</billingname>,
  <shortname>{data($row/@tklast)}</shortname>)}
<title code="{data($row/@tktitle)}">{data($row/@tktitle)}</title>
<titlesort>{data($row/@tisort)}</titlesort>
<rank>{data($row/@tksort)}</rank>
<gender />
<location code="{data($row/@tkloc)}">{data($row/@ldesc)}</location>
</timekeeper>
<date type="timecard">{substring($row/@tworkdt,1,10)}</date>
<status>{data($row/@tstatus)}</status>
<print-flag>{data($row/@tprntfl)}</print-flag>
<fixed-fee-activity code="{data($row/@tcode)}">
<name>{data($row/@actname)}</name>
<line id="1">{data($row/@adesc1)}</line>
<line id="2">{data($row/@adesc2)}</line>
<line id="3">{data($row/@adesc3)}</line>
<line id="4">{data($row/@adesc4)}</line>
<line id="5">{data($row/@adesc5)}</line>
<line id="6">{data($row/@adesc6)}</line>
<line id="7">{data($row/@adesc7)}</line>
<line id="8">{data($row/@adesc8)}</line>
<line id="9">{data($row/@adesc9)}</line>
<line id="10">{data($row/@adesc10)}</line>
</fixed-fee-activity>
<card-values>
<card-value type="billed">
<rate>{data($row/@tbillrt)}</rate>
<hours>{data($row/@tbillhrs)}</hours>
<amount>{data($row/@tbilldol)}</amount>
</card-value>
<card-value type="worked">
<rate>{data($row/@tworkrt)}</rate>
<hours>{data($row/@tworkhrs)}</hours>
<amount>{data($row/@tworkdol)}</amount>
</card-value>
<card-value type="standard">
<rate>{data($row/@astd)}</rate>
<hours>{data($row/@tbillhrs)}</hours>
<amount>{data($row/@astdvalue)}</amount>
</card-value>
<card-value type="timerate1">
<rate>{data($row/@timerate1)}</rate>
<hours>{data($row/@tbillhrs)}</hours>
<amount>{data($row/@timevalue1)}</amount>
</card-value>
<card-value type="timerate2">
<rate>{data($row/@timerate2)}</rate>
<hours>{data($row/@tbillhrs)}</hours>
<amount>{data($row/@timevalue2)}</amount>
</card-value>
<card-value type="split-original">
<rate>{data($row/@sptbrt)}</rate>
<hours>{data($row/@sptbhr)}</hours>
<amount>{data($row/@sptbdl)}</amount>
</card-value>
</card-values>
<split-direct>{data($row/@split_direct)}</split-direct>
<task code="{data($row/@task_code)}">{data($row/@task_description)}</task>
<activity code="{data($row/@activity_code)}">{data($row/@activity_description)}</activity>
<tax code="{data($row/@tccode)}" rate="{data($row/@tcrate)}">
<description>{data($row/@tcdesc)}</description>
<total type="tax">{data($row/@tax)}</total>
</tax>
<extension-fields>
<extension-field  id="1"  description="{data($row/@cttitl1)}">{data($row/@tudef1)}</extension-field>
<extension-field  id="2"  description="{data($row/@cttitl2)}">{data($row/@tudef2)}</extension-field>
<extension-field  id="3"  description="{data($row/@cttitl3)}">{data($row/@tudef3)}</extension-field>
<extension-field  id="4"  description="{data($row/@cttitl4)}">{data($row/@tudef4)}</extension-field>
<extension-field  id="5"  description="{data($row/@cttitl5)}">{data($row/@tudef5)}</extension-field>
</extension-fields>
</root>'))

And now you have successfully customized your timecard output in the XML superbill.

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]

Short Paid Invoices

Invoices that are partially paid are a recurring issue at many law firms. The following query will return current day short paid invoices, their original value, the current day payment and the balance left on the invoice. You'll notice that I did not use common table expressions in this query. The reason for this is that this query was developed for an Extend event, and in Extend the final line must always start with SELECT, so starting with WITH won't work!

SELECT 
Invoices.InvMatter
,clname1 AS 'Client Name'
,mdesc1 AS 'Matter Description'
,tkinit AS 'BA #'
,tklast+', '+tkfirst AS 'BillAty'
,ldesc AS 'BA Location'
,Invoices.invInvoice
,Invoices.invDate
,Invoices.invFeeAmt
,Invoices.invCostAmt
,Invoices.invAmount
,Credits.pmtToday
,Credits.woffToday
,Invoices.invAmount-Credits.pmtAmount-Credits.woffAmount AS Balance
FROM
(
SELECT
linvoice AS invInvoice
,lmatter AS invMatter
,ltradat AS invDate
,SUM(CASE WHEN lcfco='F' THEN lamount ELSE 0 END) AS invFeeAmt
,SUM(CASE WHEN lcfco='C' THEN lamount ELSE 0 END) AS invCostAmt
,SUM(lamount) AS invAmount
FROM ledger
INNER JOIN ledcode ON llcode=lccode
WHERE lcfco IS NOT NULL
GROUP BY linvoice,lmatter,ltradat
) AS Invoices
INNER JOIN
(
SELECT
inv.linvoice AS invInvoice
,inv.lmatter AS invMatter
,inv.ltradat AS invDate
,SUM(CASE WHEN ccode.lccollhs='Y' AND cred.ltradat >= GETDATE()-1 THEN cred.lamount ELSE 0 END) AS pmtToday
,SUM(CASE WHEN ccode.lccollhs='N' AND cred.ltradat >= GETDATE()-1 THEN cred.lamount ELSE 0 END) AS woffToday
,SUM(CASE WHEN ccode.lccollhs='Y' THEN cred.lamount ELSE 0 END) AS pmtAmount
,SUM(CASE WHEN ccode.lccollhs='N' THEN cred.lamount ELSE 0 END) AS woffAmount
FROM ledger cred
INNER JOIN ledcode ccode ON cred.llcode=ccode.lccode
INNER JOIN ledger inv ON cred.laptoin=inv.lindex
INNER JOIN ledcode icode ON inv.llcode=icode.lccode
WHERE ccode.lcfco IS NULL
AND icode.lcfco IS NOT NULL
AND cred.lzero <> 'R'
AND EXISTS (SELECT * FROM ledger p INNER JOIN ledcode pc ON p.llcode=pc.lccode WHERE pc.lcfco IS NULL AND p.ltradat >= GETDATE()-1
    AND p.linvoice=cred.linvoice
    AND p.lmatter=cred.lmatter
    AND p.lzero<> 'R')
GROUP BY inv.linvoice,inv.lmatter,inv.ltradat
) AS Credits ON Invoices.invInvoice=Credits.invInvoice
   AND Invoices.invMatter=Credits.invMatter
   AND Invoices.invDate=Credits.invDate
INNER JOIN matter ON Invoices.invMatter=mmatter
INNER JOIN timekeep ON mbillaty=tkinit
INNER JOIN client ON mclient=clnum
INNER JOIN location ON tkloc=locode
WHERE Invoices.invAmount-Credits.pmtAmount-Credits.woffAmount <> 0

1099 Review Data

At my firm, we have not used Elite's native 1099 reporting app within Enterprise for several years. Instead, we opt to export information from Enterprise and import into a third party 1099 application, which in our case is Convey. As such, we use a SQL based report to review all our transactions for correct flagging. This is done in two parts: 1) a summary by vendor where the vendor, voucher or address 1099 flag is not "N", and 2) a listing of all new vendors.

Part 1

WITH trust AS
(
SELECT trvendor
,trvendoraddr
,tr1099flag
,SUM(-tramount/cdrate) AS 'tramt'
FROM trcheck
INNER JOIN trsttran ON trcheck.trindex=trsttran.trindex
INNER JOIN tracct ON trsttran.tracct=tracct.tracct
INNER JOIN matter ON trmatter=mmatter
INNER JOIN currates ON trcurcd=curcode AND trdate bETWEEN cddate1 AND cddate2 AND currates.trtype='A'
WHERE YEAR(trdate)='2014'
AND tr1099flag IS NOT NULL
AND tr1099flag <> 'N'
AND trsttran.trflag IS NULL
GROUP BY trvendor,trvendoraddr,tr1099flag
), apamt AS
(
SELECT
csddt.apnum
,csddt.apaddid
,csddt.ap1099
,SUM(csddt.amt/currates.cdrate) AS 'apamt'
FROM
csddt
INNER JOIN csd ON csddt.cknum = csd.cknum AND csddt.baid = csd.baid
INNER JOIN currates ON csddt.currency=currates.curcode AND csddt.ckdate BETWEEN cddate1 AND cddate2 AND currates.trtype='A'
WHERE
YEAR(csddt.ckdate)='2014'
AND csddt.ap1099 <> 'N'
GROUP BY csddt.apnum,csddt.apaddid,csddt.ap1099
)
SELECT
apaddr.apnum
,apaddr.apaddid
,CASE WHEN apaddr.apfed IS NULL THEN 'zNoTIN'+ap.apnum ELSE apaddr.apfed END AS 'TIN'
,ap.apname AS 'Vendor Name'
,apaddr.appynm AS 'Payee'
,apaddr.appya1 AS 'Address 1'
,apaddr.appya2 AS 'Address 2'
,apaddr.appyct AS 'City'
,apaddr.appyst AS 'State'
,apaddr.appyzp AS 'Zip Code'
,ap.apct1 AS 'Vendor Type'
,ap.ap1099 AS 'Vendor 1099'
,apaddr.ap1099 AS 'Addr 1099'
,CASE WHEN apamt.ap1099 IS NULL THEN tr1099flag ELSE apamt.ap1099 END AS 'Tran 1099'
,ISNULL(apamt,0)+ISNULL(tramt,0) AS 'amt'
FROM apaddr
INNER JOIN ap ON apaddr.apnum=ap.apnum
LEFT OUTER JOIN apamt ON apaddr.apnum=apamt.apnum AND apaddr.apaddid=apamt.apaddid
LEFT OUTER JOIN trust ON apaddr.apnum=trvendor AND apaddr.apaddid=trvendoraddr
WHERE (ISNULL(apamt,0)+ISNULL(tramt,0))<>0
ORDER BY CASE WHEN apaddr.apfed IS NULL THEN 'zNoTIN'+ap.apnum ELSE apaddr.apfed END
,apaddr.apnum
,apaddr.apaddid

Part 2

WITH trust AS
(
SELECT trvendor
,trvendoraddr
,tr1099flag
,SUM(-tramount/cdrate) AS 'tramt'
FROM trcheck
INNER JOIN trsttran ON trcheck.trindex=trsttran.trindex
INNER JOIN tracct ON trsttran.tracct=tracct.tracct
INNER JOIN matter ON trmatter=mmatter
INNER JOIN currates ON trcurcd=curcode AND trdate bETWEEN cddate1 AND cddate2 AND currates.trtype='A'
WHERE YEAR(trdate)='2014'
--AND tr1099flag IS NOT NULL
--AND tr1099flag <> 'N'
AND trsttran.trflag IS NULL
GROUP BY trvendor,trvendoraddr,tr1099flag
), apamt AS
(
SELECT
csddt.apnum
,csddt.apaddid
,csddt.ap1099
,SUM(csddt.amt/currates.cdrate) AS 'apamt'
FROM
csddt
INNER JOIN csd ON csddt.cknum = csd.cknum AND csddt.baid = csd.baid
INNER JOIN currates ON csddt.currency=currates.curcode AND csddt.ckdate BETWEEN cddate1 AND cddate2 AND currates.trtype='A'
WHERE
YEAR(csddt.ckdate)='2014'
AND csddt.trtype<>'CA'
--AND csddt.ap1099 <> 'N'
GROUP BY csddt.apnum,csddt.apaddid,csddt.ap1099
)
SELECT
apaddr.apnum
,apaddr.apaddid
,CASE WHEN apaddr.apfed IS NULL THEN 'zNoTIN'+ap.apnum ELSE apaddr.apfed END AS 'TIN'
,ap.apname AS 'Vendor Name'
,apaddr.appynm AS 'Payee'
,apaddr.appya1 AS 'Address 1'
,apaddr.appya2 AS 'Address 2'
,apaddr.appyct AS 'City'
,apaddr.appyst AS 'State'
,apaddr.appyzp AS 'Zip Code'
,ap.apopendt AS 'Open Date'
,ap.apct1 AS 'Vendor Type'
,ap.ap1099 AS 'Vendor 1099'
,apaddr.ap1099 AS 'Addr 1099'
,CASE WHEN apamt.ap1099 IS NULL THEN tr1099flag ELSE apamt.ap1099 END AS 'Tran 1099'
,ISNULL(apamt,0)+ISNULL(tramt,0) AS 'amt'
FROM apaddr
INNER JOIN ap ON apaddr.apnum=ap.apnum
LEFT OUTER JOIN apamt ON apaddr.apnum=apamt.apnum AND apaddr.apaddid=apamt.apaddid
LEFT OUTER JOIN trust ON apaddr.apnum=trvendor AND apaddr.apaddid=trvendoraddr
WHERE (ISNULL(apamt,0)+ISNULL(tramt,0))<>0
AND YEAR(ap.apopendt)='2014'
ORDER BY CASE WHEN apaddr.apfed IS NULL THEN 'zNoTIN'+ap.apnum ELSE apaddr.apfed END
,apaddr.apnum
,apaddr.apaddid

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)

Billing Volumes by Biller

The following query summarizes the quantity and value of non-reversed of invoices billed by user (biller). This particular query assumes billers are set up as timekeepers. If they are not, replace the tklast + tkfirst with ufullname from the usmaster table (joined to the uaccess table). We also use a UDF to define whether a matter is eBilled (udf index 73 shown below), and results are divided between eBilled and non-eBilled. Also, we define hourly and contigent matters as the presence or absence of a value in the Projected Fees field in master matter (mffee field in the matter table). Results are also split based on this field.

SELECT
tklast+', '+tkfirst AS 'User'
,COUNT(DISTINCT CASE WHEN (udvalue<>'Y' OR udvalue IS NULL) AND mffee IS NULL THEN linvoice ELSE NULL END) AS 'Hourly Non-eBill #'
,SUM(CASE WHEN (udvalue<>'Y' OR udvalue IS NULL) AND mffee IS NULL THEN lamount/cdrate ELSE 0 END) AS 'Hourly Non-eBill $'
,COUNT(DISTINCT CASE WHEN udvalue='Y' AND mffee IS NULL THEN linvoice ELSE NULL END) AS 'Hourly eBill #'
,SUM(CASE WHEN udvalue='Y' AND mffee IS NULL THEN lamount/cdrate ELSE 0 END) AS 'Hourly eBill $'
,COUNT(DISTINCT CASE WHEN mffee IS NULL THEN linvoice ELSE NULL END) AS 'Hourly Total #'
,SUM(CASE WHEN mffee IS NULL THEN lamount/cdrate ELSE 0 END) AS 'Hourly Total $'
,COUNT(DISTINCT CASE WHEN (udvalue<>'Y' OR udvalue IS NULL) AND mffee IS NOT NULL THEN linvoice ELSE NULL END) AS 'Cont Non-eBill #'
,SUM(CASE WHEN (udvalue<>'Y' OR udvalue IS NULL) AND mffee IS NOT NULL THEN lamount/cdrate ELSE 0 END) AS 'Cont Non-eBill $'
,COUNT(DISTINCT CASE WHEN udvalue='Y' AND mffee IS NOT NULL THEN linvoice ELSE NULL END) AS 'Cont eBill #'
,SUM(CASE WHEN udvalue='Y' AND mffee IS NOT NULL THEN lamount/cdrate ELSE 0 END) AS 'Cont eBill $'
,COUNT(DISTINCT CASE WHEN mffee IS NOT NULL THEN linvoice ELSE NULL END) AS 'Cont Total #'
,SUM(CASE WHEN mffee IS NOT NULL THEN lamount/cdrate ELSE 0 END) AS 'Cont Total $'
,COUNT(DISTINCT linvoice) AS 'Total #'
,SUM(lamount/cdrate) AS 'Total $'
FROM ledger
INNER JOIN ledcode ON llcode=lccode
INNER JOIN batch ON bbatch=lbatch
INNER JOIN uaccess ON bop=uname
INNER JOIN timekeep ON wvtkinit=tkinit
INNER JOIN periodt ON lperiod=pe
INNER JOIN matter ON mmatter=lmatter
INNER JOIN location ON mloc=locode
INNER JOIN client ON mclient=client.clnum
INNER JOIN client cr ON client.crelated=cr.clnum
INNER JOIN currates ON mcurrency=curcode AND trtype='A' AND pebedt BETWEEN cddate1 AND cddate2
LEFT OUTER JOIN udf ON lmatter=udjoin AND udfindex=73
WHERE pebedt BETWEEN '1/1/2013' AND '12/31/2013'
AND lcfco IS NOT NULL
AND lzero <> 'R'
GROUP BY tklast,tkfirst
ORDER BY COUNT(DISTINCT linvoice) DESC

I've also included a slightly different query below, which measures total invoice output by biller for the current month.

SELECT  tklast+', '+tkfirst AS 'Biller' 
,peendt 
,COUNT(DISTINCT linvoice) AS 'NumInvs' 
,SUM(CASE WHEN lcfco='F' THEN lamount/cdrate ELSE 0 END) AS 'Fees' 
,SUM(CASE WHEN lcfco='C' THEN lamount/cdrate ELSE 0 END) AS 'Costs' 
,SUM(CASE WHEN lcfco NOT IN ('F','C') THEN lamount/cdrate ELSE 0 END) AS 'Other' 
,SUM(lamount/cdrate) AS 'Total' 
,SUM(lamount/cdrate)/COUNT(DISTINCT linvoice) AS 'AvgInv' 
FROM ledger 
INNER JOIN batch ON lbatch=bbatch 
INNER JOIN ledcode ON llcode=lccode 
INNER JOIN uaccess ON bop=uname 
INNER JOIN timekeep ON wvtkinit=tkinit 
INNER JOIN periodt ON lperiod=pe 
INNER JOIN matter ON lmatter=mmatter 
INNER JOIN currates ON mcurrency=curcode AND currates.trtype='A' AND periodt.pebedt BETWEEN currates.cddate1 AND currates.cddate2 
WHERE lcfco IS NOT NULL 
AND peendt = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)-1  
GROUP BY tklast,tkfirst,peendt