Payments by Invoice Ledger Code

This is a simple little query to see how payments from a particular client have been applied by the original invoice ledger code. For example, you can determine how much of the payments were applied to fees or costs. You may need to tinker with the criteria to match your setups. This uses the matter description udf.

SELECT 
mmatter AS 'Matter #'
,dbo.udfMattDesc(mmatter) AS 'Matter Description'
,SUM(CASE WHEN inv.llcode = 'FEES' THEN pmt.lamount ELSE 0 END) as 'Fees Collected'
,SUM(CASE WHEN inv.llcode <> 'FEES' THEN pmt.lamount ELSE 0 END) AS 'Costs Recovered'
,SUM(pmt.lamount) AS TOTAL
FROM ledger AS pmt
INNER JOIN matter ON pmt.lmatter=mmatter
INNER JOIN ledger AS inv ON pmt.laptoin=inv.lindex
WHERE YEAR(pmt.ltradat)='2009'
AND mclient='014523'
AND pmt.llcode LIKE 'PAY%'
GROUP BY mmatter