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

Working Timekeeper Collections for a Specific Billing Timekeeper

This query will return a summary of collections by working timekeeper for a specific billing timekeepers historical matters. This will give you insights as to who exactly is working on the matters of a specific billing attorney. Included in this query is also an example of how to use the OVER() function available to those on SQL Server 2005 and 2008. The OVER() function allows you to sum the contents of a column to facilitate, among other things, creating a percent-to-total value for each row without an additional call to the database. This query also uses the timekeeper name user defined function.

The core of this query was submitted by Penny Turner, IS Director at Smith Haughey Rice & Roegge and was edited to include the percent to total functionality and grand total. Many thanks to Penny!

SELECT mttk AS 'TK #',
dbo.udfTKFullName(mttk) AS 'Working TK',
Collections,
CAST(1.00*Collections/SUM(Collections) OVER() AS DECIMAL(16,4)) AS 'Pct to Total'
FROM
(
SELECT mttk,
tklast,
SUM(mtdocodc) AS 'Collections'
FROM mattimhs
INNER JOIN matter ON mtmatter=mmatter
INNER JOIN hmatter ON hmatter=mmatter AND mbillaty=hmbaty
INNER JOIN timekeep ON tkinit=mttk
INNER JOIN periodt ON mtper=pe
WHERE YEAR(peendt) = '2009'
AND hmbaty ='2100'
AND pebedt BETWEEN hmdate1 AND hmdate2
AND mtdocodc<>0
GROUP BY mttk,tklast
) AS a
UNION
SELECT 'GT','Grand Total',SUM(mtdocodc),NULL
FROM mattimhs
INNER JOIN matter ON mtmatter=mmatter
INNER JOIN hmatter ON hmatter=mmatter AND mbillaty=hmbaty
INNER JOIN timekeep ON tkinit=mttk
INNER JOIN periodt ON mtper=pe
WHERE YEAR(peendt) = '2009'
AND hmbaty ='2100'
AND pebedt BETWEEN hmdate1 AND hmdate2
AND mtdocodc<>0