Grant Fritchey (9/17/2012)
ISNULL and COALESCE on columns in the where clause are going to hurt performance....
This should be close to a SARGable version of the query:
SELECT DISTINCT COALESCE(f.FranchiseNumber, di.FranchiseNumber) AS FranchiseNumber,
a.CustomerID,
a.EmailAddress AS EmailBefore,
c.EmailAddress AS EmailAfter,
a.ChangeDate,
j.ScheduledEndDate AS MoveDate,
CASE
WHEN j.ScheduledEndDate < a.ChangeDate THEN 'After Move Change'
ELSE 'Before move change'
END AS Change
FROM dbo.tblEmailAudit a
INNER JOIN dbo.tblCustomer c
ON c.CustomerID = a.CustomerID
AND c.DatabaseInfoID = a.DatabaseInfoID
and (
c.EmailAddress <> a.EmailAddress
OR (c.EmailAddress IS NULL AND a.EmailAddress IS NOT NULL)
OR (c.EmailAddress IS NOT NULL AND a.EmailAddress IS NULL)
)
INNER JOIN dbo.tblWorkOrder wo
ON c.CustomerId = wo.CustomerId
AND wo.DatabaseInfoId = a.DatabaseInfoId
INNER JOIN dbo.tblJob j
ON j.WorkOrderID = wo.WorkOrderID
AND j.DatabaseInfoID = a.DatabaseInfoID
LEFT JOIN dbo.tblDatabaseInfo di
ON di.DatabaseInfoID = a.DatabaseInfoID
AND di.FranchiseNumber = 253
LEFT JOIN dbo.tblFranchise f
ON (f.FranUniqueID = a.FranUniqueID OR f.DatabaseInfoID = a.DatabaseInfoID)
--a.DatabaseInfoID = f.DatabaseInfoID
AND f.FranchiseNumber = 253
--AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
WHERE 1 = 1
--(COALESCE(f.FranchiseNumber, di.FranchiseNumber) = 253)
AND j.ScheduledEndDate < a.ChangeDate
--AND ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'')
ORDER BY a.ChangeDate DESC;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden