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

Joint Invoice View

Often times it is helpful to know whether or not an invoice is a single matter or joint invoice. The following view is a pretty simple query that determines the answer to that question. You can then join this view to any query to include or exclude joint invoices from your results (or treat them differently).

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[v_jointinv]
AS
SELECT
linvoice,
COUNT(DISTINCT lmatter) AS NumMtr,
CASE WHEN COUNT(DISTINCT lmatter) >1 THEN 'Yes' ELSE 'No' END AS JointInv
FROM
ledger
INNER JOIN ledcode ON llcode=lccode
WHERE
lcfco IS NOT NULL
GROUP BY linvoice
GO

Missing Records in Whitehill Document Catalog

A while back we looked at missing files in Whitehill where the appropriate record existed in the Document Catalog. Sometimes, however, the Document Catalog is never updated. The following query will help identify which invoices have been created and not inserted in the Document Catalog.

SELECT linvoice
, ltradat
, lmatter
, clname1
, mdesc1
, bop
, SUM(lamount) AS invamount
FROM ledger
INNER JOIN matter ON lmatter=mmatter
INNER JOIN client ON mclient=clnum
INNER JOIN batch ON lbatch=bbatch
WHERE ltradat >= '5/1/2010'
AND lzero <> 'R'
AND linvoice <> '0'
AND llcode IN ('FEES','HCOST','SCOST')
AND mclient <> '123456'
AND linvoice NOT IN
 (
 SELECT CAST(EMP_ID AS INT)
 FROM Document_Catalog
 WHERE EMP_ID NOT IN ('DRAFT','SEC_9999','TK_9999')
 )
AND linvoice NOT IN (SELECT linvoice FROM v_jointinv WHERE NumMtr > 1)
AND batch.bop NOT IN ('abc','def')
GROUP BY linvoice,ltradat,lmatter,mdesc1,clname1,bop
ORDER BY bop,linvoice

You'll notice in the code above, I've set some exclusions. We have a view that counts the number of matters per invoice. Any invoices with more than one matter (e.g. a joint invoice) are excluded from this query and are included in a separate query that doesn't include matter information. I've also included the ability to exclude particular users and clients from the query. Regarding the subquery on the Document Catalog table, any non-numeric values will need to be excluded to enable the matching against the EMP_ID field (or you can cast linvoice as VARCHAR).