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