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