Friday, November 6, 2009

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

2 comments:

  1. Hello John!

    I can’t thank you enough for developing this page. It has been a tremendous help to our firm.

    I ran the “Working Timekeeper Collections Query from November 6th in our environment. We do not use the Full Name UDF field so I commented it out. I received the following error when executing the query. Any ideas?

    “Msg 205, Level 16, State 1, Line 1
    All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.”

    Thanks!
    Brian
    Larkin Hoffman
    Minneapols

    ReplyDelete
  2. Hi Brian!

    Thanks for the kind comments. You have two choices here, you can either add tklast where the udf was or remove "Grand Total," from the SELECT after the UNION. Thanks!

    ReplyDelete