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'
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.
Labels:
billings,
enterprise,
sql,
wip
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment