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 Description	WIP Hours	WIP Fees	WIP Costs	Total WIP
1000-001	Triad v. Magnuson	201.06	148033.265	2260.93	150294.195
1000-002	Triad v. Hanover Insurance	94.65	27254.01	901.70	28155.71
1000-003	Triad v. Keener Mfg.	0.00	0.00	1500.30	1500.30
1000-004	Triad v. Markham	49.59	12902.82	110.71	13013.53
1000-005	Triad Gadget Claims (Split Party #1)	28.05	8631.85	1000.00	9631.85
1000-006	Triad Gadget Claims (Split Party #2)	35.64	12453.15	1500.00	13953.15
1000-007	Triad Gadget Claims (Split Billing File)	8.40	2890.00	0.00	2890.00
1000-008	Triad v. Kilkenny Design	19.50	1680.02	2500.00	4180.02
1000-009	Practice Development - Triad	29.50	8418.75	6175.00	14593.75
1000-015	Tessting lee	0.00	0.00	100.00	100.00