Friday, October 30, 2009

Current Proformas

Occasionally I will find myself in need of a list of current proformas pertaining to a specific client or timekeeper. The following code will pull a list of current proformas along with the number and amount of fee entries and number and amount of cost entries. I've used a beginning date in the query that is rather arbitrary, however, some 'recent' date was needed to remove anomalies from conversions long ago. This query uses the Matter Description user defined function.
SELECT
mmatter AS 'Matter #',
clname1 AS 'Client Name',
dbo.udfMattDesc(mmatter) AS 'Matter Description',
prohead.phindex AS 'Proforma #',
phdate AS 'Date',
dbo.udfTKFullName(mbillaty) AS 'Billing Aty',
COUNT(timecard.tindex) AS '# Timecards',
ISNULL(SUM(timecard.tbilldol),0) AS 'Fee Amount',
COUNT(cost.cindex) AS '# Costcards',
ISNULL(SUM(cost.cbillamt),0) AS 'Cost Amount'
FROM prohead
INNER JOIN matter ON mmatter=phmatter
INNER JOIN client ON mclient=clnum
INNER JOIN timekeep ON mbillaty=tkinit
INNER JOIN prodetail ON prohead.phindex=prodetail.phindex
LEFT OUTER JOIN cost ON prodetail.pddetail=cost.cindex AND pdtype='C' AND cinvoice IS NULL
LEFT OUTER JOIN timecard ON prodetail.pddetail=timecard.tindex AND pdtype='T' AND tinvoice IS NULL
WHERE
phdate >= '1/1/09'
AND phstatus LIKE 'C%'
GROUP BY mmatter,
clname1,
mbillaty,
prohead.phindex,
phdate
HAVING COUNT(timecard.tindex)>0 OR COUNT(cost.cindex)>0
ORDER BY prohead.phindex

No comments:

Post a Comment