Tuesday, September 8, 2009

Matter List having Collections

The SQL code below will return a list of matters, with client name, city state and billing timekeeper for all matters of a given department that have had collections in 2008 or 2009.
matter.mmatter AS 'Matter #',
client.clname1 AS 'Client Name',
dbo.udfMattDesc(matter.mmatter) AS 'Matter Description',
ISNULL(matter.mcontact,client.clcontact) AS 'Contact',
client.clcity AS 'City',
client.clstate AS 'State',
dbo.udfTKFullName(matter.mbillaty) AS 'Billing Attorney'
INNER JOIN client ON matter.mclient=client.clnum
INNER JOIN timekeep ON matter.mbillaty=timekeep.tkinit
WHERE matter.mdept = 'D'
  (SELECT * FROM matths
  WHERE matter.mmatter=matths.mhmatter
  AND RIGHT(matths.mhper,2) IN ('08','09')
  AND matths.mhdocodc <> 0)
ORDER BY clname1,mmatter
As you can see, the above query utilizes the two user defined functions mentioned below.

No comments:

Post a Comment