Thursday, October 15, 2009

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.
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
 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
 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'

No comments:

Post a Comment