Tuesday, December 15, 2009

Work in Process by Matter

The query below give you a list of matters with unbilled fees and costs. This query utilizes a feature in SQL Server 2005 called Common Table Expressions (or CTEs). You can use CTEs within the query rather than embedding multiple sub-queries. In this case, I use two CTEs to build a 'table' of unbilled time by matter and a 'table' of unbilled costs by matter. I then link those CTEs to the matter table using LEFT OUTER JOINs to make sure we get all the data. This also uses the Matter Description UDF.

WITH wiptime AS (
SUM(tbillhrs) AS wiphrs,
SUM(tbilldol) AS wipfees
FROM timecard
AND tinvoice IS NULL
GROUP BY tmatter),
wipcosts AS (SELECT
SUM(cbillamt) AS wipcosts
FROM cost
AND cinvoice IS NULL
GROUP BY cmatter)
SELECT mmatter AS 'Matter #',
dbo.udfMattDesc(mmatter) AS 'Matter Description',
ISNULL(wiphrs,0) AS 'WIP Hours',
ISNULL(wipfees,0) AS 'WIP Fees',
ISNULL(wipcosts,0) AS 'WIP Costs',
ISNULL(wipfees,0)+ISNULL(wipcosts,0) AS 'Total WIP'
FROM matter
LEFT OUTER JOIN wiptime ON mmatter=tmatter
LEFT OUTER JOIN wipcosts ON mmatter=cmatter
WHERE mclient = '1000'
AND (ISNULL(wipfees,0)+ISNULL(wipcosts,0)) <> 0
Output Example

Matter #Matter DescriptionWIP HoursWIP FeesWIP CostsTotal WIP
1000-001Triad v. Magnuson201.06148033.2652260.93150294.195
1000-002Triad v. Hanover Insurance94.6527254.01901.7028155.71
1000-003Triad v. Keener Mfg.0.000.001500.301500.30
1000-004Triad v. Markham49.5912902.82110.7113013.53
1000-005Triad Gadget Claims (Split Party #1)28.058631.851000.009631.85
1000-006Triad Gadget Claims (Split Party #2)35.6412453.151500.0013953.15
1000-007Triad Gadget Claims (Split Billing File)8.402890.000.002890.00
1000-008Triad v. Kilkenny Design19.501680.022500.004180.02
1000-009Practice Development - Triad29.508418.756175.0014593.75
1000-015Tessting lee0.000.00100.00100.00


  1. I never knew you could do joins to other queries like that. I always used subqueries but subqueries only allow one value to be returned.

  2. Hi Tim. Thanks for the comment. This is apparently a new feature in SQL Server 2005 and is definitely one I'll be utilizing a lot. It is a handy alternative to views and temp tables for sure.

  3. I also noticed on your function udfMattDesc you are setting it as a varchar(180). To be technical it could go to 183 if all sixty characters in each line are used plus the 3 spaces you add.

    Addimitedly we have 80K+ matters and only one broke 180 with the three spaces. The final character gets dropped in the function.

  4. Great point Tim. I've updated the udf to varchar(185). (threw a couple extra in for good measure)

  5. What field would I use if I wanted to use this script and results, for only for WIP that has time entry for the 15th of the previous month and earlier?