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]