Optimize the SQL query

ALTER PROCEDURE [dbo].[unbilledMISReport]
	-- Add the parameters for the stored procedure here
	@uploaddate as varchar(20)
AS
BEGIN
	SET NOCOUNT ON;
declare @sql nvarchar(max)
set @sql =
'Select HAR.PersonNo as PersonNo,HAR.EmployeeName as EmployeeName,HAR.Period as Period,'+
'HAR.ProfitCenter as ProfitCenter,HAR.CostCenter as CostCenter,HAR.ProjectTime as ProjectTime,'+
'YTC.Text as Text,Null as Status,Null as Remark,HAR.Account as Account, HAR.ProjectNumber as ProjectNumber,'+
'CONVERT(VARCHAR(10),HAR.Date, 120) as Date,PC.ProjectTLE as ProjectTLE,EMD.BaseLocation as BaseLocation,'+
'ETES.ManagerName as ManagerName,EMD.ORGManager1 as ORGManager1,HAR.Vertical as Vertical,'+
'ICE.Manual_Auto as Manual_Auto,ICE.GDS_NonGDS as GDS_NonGDS,'+
'convert(varchar(10),GPS.ResourceStartDate, 120) as ResourceStartDate,convert(varchar(10),GPS.ResourceEndDate, 120) as ResourceEndDate,'+
'GPS.Status as Status_Act, GPS.BillingKey as BK,PC.SoldToPt as SoldToPt,AM.AccountDescription as AccountDescription,'+
'ETES.EmployeeEmailID as EmployeeEmail,ETES.ManagerEmailID as EtesManagerEmail,'+
'CONVERT(VARCHAR(10),HAR.UploadDate, 120) As UploadDate  '+

'From WeeklyHARReport HAR '+

'left outer Join (select * from (select x.* ,row_number() over (partition by (cast(PersonNo as varchar)+cast(cast(ObjectID as datetime) as varchar)) order by objectid ) as rn from YTCError as x) as y where rn=1) as YTC '+
'on (cast(HAR.PersonNo as varchar) + Cast(cast(HAR.Date as datetime)as varchar)) = '+
'(cast(YTC.PersonNo as varchar) + cast(cast(YTC.ObjectID as datetime) as varchar)) '+

'left outer Join (select * from (select a.* ,row_number() over (partition by ShortIdentification order by ShortIdentification) as rn1 from ProjectConsolidated as a) as b where rn1=1 ) as PC '+
'on HAR.ProjectNumber = PC.ShortIdentification  '+

'left outer Join (select * from (select a.* ,row_number() over (partition by EmpID order by EmpID) as rn1	from ETesMgrEmp as a) as b where rn1=1 ) as ETES '+
'on HAR.PersonNo = ETES.EmpID  '+

'left outer Join (select * from (select a.* ,row_number() over (partition by EmployeeID order by EmployeeID) as rn1	 from EmanageDump as a) as b where rn1=1 ) as EMD '+
'on HAR.PersonNo = EMD.EmployeeID '+

'left outer Join (select * from (select a.* ,row_number() over (partition by SoldToParty order by SoldToParty) as rn1	from ICEntities as a) as b where rn1=1 ) as ICE '+
'on PC.SoldToPt = ICE.SoldToParty '+

'left outer Join (select * from (select a.* ,row_number() over (partition by (WBSCode + EmployeeID) order by EmployeeID) as rn1	  from GPSF as a) as b where CONVERT(VARCHAR(10),UploadDate, 120) = '''+  @uploaddate +''') as GPS '+
'on (HAR.PersonNo + PC.ProjectTLE)  = (GPS.WBSCode + GPS.EmployeeID) '+

'left outer Join (select * from (select a.* ,row_number() over (partition by Account order by Account) as rn1	from AccountMaster as a) as b where rn1=1 ) as AM '+
'on HAR.Account = AM.Account '+

'Where  '+

'CONVERT(VARCHAR(10),HAR.UploadDate, 120) = '''+  @uploaddate +'''   and HAR.PersonNo is not null '+

'order by HAR.EmployeeName '

--print @sql


exec sp_executesql @sql,N'@uploaddate varchar(20)',@uploaddate  
END

Select allOpen in new window