Proforma Checks

Each month I run the following two queries to double-check our proforma runs. The first is a count by batch with beginning and ending proforma numbers and the second is similar, but by timekeeper.

Number of Proformas by Batch

SELECT
phbatch AS 'Batch No.',
bop AS Operator,
min(phindex) AS 'First Proforma',
max(phindex) AS 'Last Proforma',
count(phindex) AS '# of Proformas'
FROM prohead
INNER JOIN batch ON phbatch = bbatch
WHERE bper = '1209'
GROUP BY phbatch,bop

Number of Proformas by Timekeeper

SELECT tkloc AS 'Location',
phbatch AS 'Batch No.',
tkinit AS 'Tk #',
tklast AS 'Last Name',
MIN(phindex) AS 'First Proforma',
MAX(phindex) AS 'Last Proforma',
COUNT(phindex) AS '# of Proformas'
FROM matter
INNER JOIN prohead ON phmatter = mmatter
INNER JOIN timekeep ON tkinit = mbillaty
INNER JOIN batch ON phbatch = bbatch
WHERE bper = '1209'
GROUP BY tkloc,phbatch,tkinit,tklast
ORDER BY phbatch,tkloc,tkinit

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