I am using sub queries using joins. For 11946 rows, the query is taking 18 seconds.
Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName,
Coalesce(Error1, '')
+ CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END
+ Coalesce(Error2, '')
+ CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END
+ Coalesce(Error3, '') as ErrorList
From(
select [Date], ClientName,
CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,
CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END as Error2,
CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END as Error3
From
(Select
[Date],
s.clientID,
c.DisplayName [ClientName],
si.PayorID [SchedPayor],
ce.PayorID [EligPayor],
stf.DisplayName [StaffName]
From Schedule s
INNER JOIN ScheduleItem si ON s.ScheduleID = si.ScheduleID and s.AccountID = si.AccountID and si.Owner = 1
INNER JOIN Client c ON s.ClientID = c.ClientID and s.AccountID = c.AccountID
OUTER APPLY (Select ClientID, PayorID From ClientEligibility ce Where s.ClientID = ce.ClientID and s.[Date] BETWEEN ce.StartDate and ce.EndDate) as ce
LEFT JOIN Staff stf ON s.StaffID = stf.StaffID and s.AccountID = stf.AccountID
WHERE
s.accountID = 1) as a) as b
Is there any other way to write query to optimize performance?