Firstly make sure you have appropriate indexes on the tables - see your previous post
the following is my stored procedure can any one suggest me the improvement of execution time[
^]
Next - why are you doing the conversions on dates
CONVERT(Varchar(12),DateOfEntry,101)DateOfEntry
...
CONVERT(Varchar(12),TargetDate,101)TargetDate
Don't do the conversion in the data retrieval - only do that in the presentation layer.
You're joining across a lot of tables and using OR in the ON clauses - is there any way you can reduce the number of records that are being fed into these joins - e.g. use a CTE to get the key records with the WHERE clause then join to get the additional information.
You're also calling two functions
dbo.getDefectlistfromName(RefBy) RefBy,
dbo.GuiId(EquipmentGuid)EquipmentGuid,
Are they efficient (you haven't shared that code)? Do they actually need to be there or can they be used immediately prior to presentation of the results