Tidying Up Of Sproc

Hi

I've written this sproc. Can any one suggest any optimisations to the
main query and its sub-queries please? Truncated DDL after


CREATE PROCEDURE dbo.spGetUnbilledInfoByClient
(
@ClientID varchar(5),
@Sort int = 0,
@Dir int = 0
) AS

SET NOCOUNT ON

-- The Main Query
SELECT mmatter, mdesc1, tkfirst + ' ' + tklast AS Contact,
CONVERT(varchar(10), mopendt, 103) As [Date], CONVERT(DECIMAL(18,2),
tTime.Cost) AS COST, tTime.Hours, tDisbursements.Disbursements



FROM matter
INNER JOIN timekeep ON mbillaty = timekeep.tkinit
lEFT JOIN (

SELECT cmatter, SUM(Disbursements) AS Disbursements FROM (
SELECT dbo.cost.cmatter,
CASE WHEN dbo.cost.cauth = 'SO' OR dbo.cost.cauth = 'CF'
THEN dbo.cost.cbillamt * 1.175
ELSE
dbo.cost.cbillamt
END AS Disbursements
FROM dbo.cost
WHERE (dbo.cost.cstatus = 'b') AND (dbo.cost.cinvoice IS NULL) AND
(LEFT(dbo.cost.cmatter, 5) = @ClientID)
) AS costs
GROUP BY cmatter

) AS tDisbursements
ON dbo.matter.mmatter = tDisbursements.cmatter

INNER JOIN
(
SELECT dbo.timecard.tmatter, SUM(dbo.timecard.tbillhrs) AS Hours,
SUM(dbo.timecard.tbilldol) AS Cost
FROM dbo.timecard JOIN dbo.batch ON dbo.timecard.tbatch =
dbo.batch.bbatch
WHERE (dbo.timecard.tinvoice IS NULL)
AND (dbo.timecard.tstatus = 'b')
AND (LEFT(dbo.timecard.tmatter, 5) = @ClientID)
AND (dbo.timecard.tbilldt IS NULL)
AND ( NOT dbo.batch.bfindt IS NULL)
GROUP BY tmatter
) AS tTime
ON dbo.matter.mmatter = tTime.tmatter

--Sort (see http://www.sqlteam.com/forums/topic...942&whichpage=3)
ORDER BY
CASE -- numeric fields
WHEN @Sort = 4 AND @Dir = 0
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 0
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 0
THEN tDisbursements.Disbursements
END DESC,

CASE -- datetime
WHEN @Sort = 0 AND @Dir = 0
THEN mopendt
WHEN @Sort = 3 AND @Dir = 0
THEN mopendt
END DESC,

CASE -- character fields
WHEN @Sort = 1 AND @Dir = 0
THEN mdesc1
WHEN @Sort = 2 AND @Dir = 0
THEN tkfirst + ' ' + tklast

END DESC,

-- ASC
CASE -- numeric fields
WHEN @Sort = 4 AND @Dir = 1
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 1
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 1
THEN tDisbursements.Disbursements
END ASC,

CASE -- datetime
WHEN @Sort = 0 AND @Dir = 1
THEN mopendt
WHEN @Sort = 3 AND @Dir = 1
THEN mopendt
END ASC,

CASE -- character fields
WHEN @Sort = 1 AND @Dir = 1
THEN mdesc1
WHEN @Sort = 2 AND @Dir = 1
THEN tkfirst + ' ' + tklast

END ASC
GO


CREATE TABLE [matter] (
[mname] [varchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[mmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[mrtcode] [smallint] NULL ,
[mdept] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[mopendt] [datetime] NULL ,
[mbillaty] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[mdesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO




CREATE TABLE [cost] (
[cindex] [int] IDENTITY (1, 1) NOT NULL ,
[cmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[camount] [money] NULL ,
[cbilldt] [datetime] NULL ,
[cbillamt] [money] NULL ,
[cinvoice] [int] NULL ,
[ccode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[cbatch] [int] NULL ,
[cauth] [varchar] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [costcode] (
[cocode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[codesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO


CREATE TABLE [timecard] (
[tindex] [int] IDENTITY (1, 1) NOT NULL ,
[tmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[ttk] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[tbilldt] [datetime] NULL ,
[tbillhrs] [decimal](16, 2) NULL ,
[tbillrt] [decimal](16, 2) NULL ,
[tbilldol] [money] NULL ,
[tinvoice] [int] NULL ,
[tcode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[tbatch] [int] NULL
) ON [PRIMARY]
GO


CREATE TABLE [batch] (
[bbatch] [int] IDENTITY (1, 1) NOT NULL ,
[btype] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[bfindt] [datetime] NULL ,
[bop] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[bper] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[bpflag] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[bdopen] [datetime] NULL ,
[btothrs] [decimal](12, 2) NULL ,
[btotdol] [money] NULL
) ON [PRIMARY]
GO