I agree with you naomi.

Best broken down into sensible pieces.

It looks like something thats had a lot bolted onto it over a long period.

>This monstrous query is very hard to read and impossible to understand. Can you break it to managable pieces using either CTE or temp tables?
>
>
>>Hi All, I've been asked to look at speeding up the following SP - my TSql is very limited. I ran Show Execution Plan and it indicated that the query's on the LetterHistory table accounted for 68% of Query cost - I have no idea as to how I can achieve the results any other way - here is the query in all it's glory
>>
>>

>>ALTER PROCEDURE [dbo].[GetClaimDetailsByMasterRefDev](@MasterRef char(10)) with recompile
>>AS
>>set dateformat dmy
>>Declare @POALetterCode int
>>Declare @PIFLetterCode int
>>Select @PIFLetterCode = dbo.GetLetterCode('PIF',@MasterRef) -- 20
>>Select @POALetterCode = dbo.GetLetterCode('POA',@MasterRef) -- 21
>>SELECT	
>>dbo.Claim.ClaimRef,
>>dbo.Claim.MasterRef,
>>ClaimStatusDesc = CASE dbo.Claim.ClaimStatus
>>	When '01' Then 'Inc' 
>>	When '02' Then 'Chk'
>>	When '03' Then 'Agr'
>>	When '04' Then 'Rej'
>>	When '05' Then 'Adm'
>>	Else '' end,
>>dbo.Claim.ClaimStatus,
>>dbo.Claim.CreditorName,
>>dbo.Claim.ClaimNarrative,
>>dbo.Claim.PayDetailsID,
>>dbo.Claim.AdmittedValUSD,
>>convert(varchar(10),dbo.ListNos.listdate,5) As AdmittedDate,
>>dbo.PaymentDetails.PayAddressID,
>>dbo.PaymentDetails.PIFStatusCode,
>>(CASE WHEN ((dbo.Claim.PayDetailsConf = 0)
>>	OR  
>>	EXISTS (Select PayDetailsID From TTDetails Where PayDetailsID = Claim.PayDetailsID
>>	And TTDetailsConf = 0)) And ClaimStatus = '05' THEN 'Y'
>>	WHEN ClaimStatus != '05' THEN ''
>>	ELSE 'N' END)
>>As PIF,
>>dbo.Master.CorrAddressID,
>>convert(varchar(10),dbo.Claim.PayDetailsConfDate,5) As PayDetailsConfDate,
>>dbo.Claim.PayDetailsConfUser,
>>dbo.Claim.PayDetailsConf,
>>PayDetailsRequestedDate = (Select convert(varchar(10),Max(LetterSentDate),5)
>>From dbo.LetterHistory Where claimref = claim.claimref and lettercode = @PIFLetterCode),
>>PayDetailsRequestedUser = (select top 1 LetterSentUser from letterhistory
>>where LetterSentDate = (select max(LetterSentDate) from LetterHistory 
>>Where LetterCode = @PIFLetterCode And ClaimRef = Claim.ClaimRef) And ClaimRef = Claim.ClaimRef),
>>(Select Distinct convert(varchar(10),Max(DateReceived),5)
>>From dbo.PayDetailsReplies
>>Where ClaimRef = Claim.ClaimRef) As PayDetailsReceiveddate,
>> (Select Top 1 InputBy 
>> from PayDetailsReplies
>> where 
>> DateReceived = (select max(DateReceived) 
>> from PayDetailsReplies 
>> Where ClaimRef = Claim.ClaimRef)) As PayDetailsReceivedUser,
>>dbo.Address.CountryCode,
>>dbo.Listnos.luxoruk,
>>dbo.Claim.AdmittedListNo,
>>dbo.multiblk.block As PayBlockCode,
>>PayBlockDescription = 
>>multiblk.block + ': - ' + rtrim(blockpay.blkd_desc) + ' - '
>>+ multiblk.raised_by + ' - ' 
>>+ convert(varchar(10),multiblk.raised_dt,5),
>>(select count(block)
>>from dbo.multiblk
>>Where active = 1
>>and clm_ref = dbo.Claim.ClaimRef) As NumBlocks,
>>(CASE WHEN EXISTS
>>(SELECT block
>>FROM dbo.multiblk
>>WHERE dbo.multiblk.block = '40' 
>>AND 
>>dbo.Multiblk.clm_ref = dbo.Claim.ClaimRef 
>>AND 
>>dbo.Multiblk.Active = 1) 
>>THEN 'Y' ELSE 'N' END) 
>>AS GadFlag,
>>(CASE WHEN EXISTS
>>(SELECT block
>>FROM dbo.multiblk
>>WHERE dbo.multiblk.block = '94' 
>>AND 
>>dbo.Multiblk.clm_ref = dbo.Claim.ClaimRef 
>>AND 
>>dbo.Multiblk.Active = 1) 
>>THEN 'Y' ELSE 'N' END) 
>>AS Deceased,
>>Art39.BenstatusCode As DBO,
>>(Select distinct(convert(varchar(10),Max(LetterSentDate),5)) 
>>from LetterHistory
>>Where LetterCode =@POALetterCode
>>And ClaimRef = Claim.ClaimRef) As POADetailsRequestedDate,
>>(select distinct(LetterSentUser)
>>from letterhistory
>>where 
>>LetterSentDate = (select distinct(Max(LetterSentDate))
>>from LetterHistory 
>>Where LetterCode = @POALetterCode)
>>And ClaimRef = Claim.ClaimRef) As POADetailsRequestedUser,
>>(Select convert(varchar(10),Max(DateReceived),5)  
>>from POADetailsReceived
>>Where ClaimRef = Claim.ClaimRef) As POADetailsReceivedDate,
>>(select distinct(InputBy) 
>>from POADetailsReceived
>>where 
>>DateReceived = (select Max(DateReceived) 
>>from POADetailsReceived 
>>Where ClaimRef = Claim.ClaimRef)) As POADetailsReceivedUser,
>>DBOReceivedDate = (Select convert(varchar(10),Max(DBOReceivedDate),5) From dbo.DBOSReceived
>>Where dbo.DBOSReceived.ClaimRef = Claim.ClaimRef),
>>DBOReceivedUser = (Select DBOReceivedUser From dbo.DBOSReceived
>>Where dbo.DBOSReceived.ClaimRef = Claim.ClaimRef And DBOReceivedDate
>>= (Select Max(DBOReceivedDate) From dbo.DBOSReceived
>>Where dbo.DBOSReceived.ClaimRef = Claim.ClaimRef)),
>>lds.due,
>>lds.dpbflag,
>>lds.adjustflag,
>>lds.empdivflag
>>From dbo.Claim 
>>Left Outer Join 
>>	Listnos
>>	ON Claim.AdmittedListNo = Listnos.AdmitList
>>Left Outer Join 
>>	Art39
>>	ON Claim.ClaimRef = Art39.ClaimRef
>>Inner JOIN
>>	dbo.Master 
>>	On  dbo.Master.MasterRef = dbo.Claim.MasterRef
>>Inner Join 
>>	dbo.Address
>>	On Master.CorrAddressID = dbo.Address.AddressID
>>Inner Join dbo.PaymentDetails
>>	On PaymentDetails.PayDetailsID = dbo.Claim.PayDetailsID
>>left outer join 
>>	lds.dbo.vw_cms_claims_main lds
>>	on dbo.Claim.ClaimRef = lds.claim_reference
>>left outer join multiblk
>>on Claim.ClaimRef = multiblk.clm_ref And multiblk.Status = 1 And multiblk.Active = 1
>>left outer join blockpay
>>On blockpay.blkd_code = multiblk.block 
>>Where dbo.Claim.MasterRef = @MasterRef
>>Order By Claim.ClaimRef
>>