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 (
SELECT
tmatter,
SUM(tbillhrs) AS wiphrs,
SUM(tbilldol) AS wipfees
FROM timecard
WHERE tbilldt IS NULL
AND tinvoice IS NULL
GROUP BY tmatter),
wipcosts AS (SELECT
cmatter,
SUM(cbillamt) AS wipcosts
FROM cost
WHERE cbilldt IS NULL
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

5 comments:

  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.

    ReplyDelete
  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.

    ReplyDelete
  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.

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

    ReplyDelete
  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?

    ReplyDelete