Year-to-Date Billed Fees and Cost with Current Work in Process

Elite provides a host of pre-written reports in it's Reports & Queues module, but most never seem to get at exactly what you need. And then when they do, the client names and matter descriptions are cut off at 20 characters (or some other insane #). The below query will give you year-to-date billed fees and costs as well as the current work in process values by matter for a specific client.

SELECT
mmatter AS 'Matter #',
dbo.udfMattDesc(mmatter) AS 'Matter Description',
ISNULL(t.feewip,0) AS 'Fee WIP',
ISNULL(c.wipcost,0) AS 'Cost WIP',
ISNULL(t.feewip,0)+ISNULL(c.wipcost,0) AS 'Total WIP',
ISNULL(t.billamt,0) AS 'Fees Billed YTD',
ISNULL(c.cbillamt,0) AS 'Costs Billed YTD',
ISNULL(t.billamt,0)+ISNULL(c.cbillamt,0) AS 'Total Billed'
FROM matter
LEFT OUTER JOIN
(
 SELECT tmatter,
 SUM(CASE WHEN tbilldt IS NULL THEN tbilldol ELSE 0 END) AS feewip,
 SUM(CASE WHEN RIGHT(tbiper,2)='09' THEN tbilldol ELSE 0 END) AS billamt
 FROM timecard
 GROUP BY tmatter
) AS t ON mmatter=t.tmatter
LEFT OUTER JOIN
(
 SELECT cmatter,
 SUM(CASE WHEN cbilldt IS NULL THEN cbillamt ELSE 0 END) AS wipcost,
 SUM(CASE WHEN RIGHT(cbiper,2)='09' THEN cbillamt ELSE 0 END) AS cbillamt
 FROM cost
 GROUP BY cmatter
) AS c ON mmatter = c.cmatter
WHERE mclient='998877'