Can this case statement and this left outer join be written more efficiently?
I have inherited some code that I need to cause to run faster. I see a
series of case statements that deal with null values and a 3-table left
outer join.
Note: I ask about both code snippets in conjunction rather than on
separate threads because I suspect the answer to both may be related.
Case Statement:
COUNT(DISTINCT TU.CRMOpportunityID) AS CRMOpportunityIDCount, CASE WHEN
TU.SalesStageName IS NULL THEN TUS.SalesStageName ELSE TU.SalesStageName
END AS SalesStageName, CASE WHEN TU.SalesStageProbability IS NULL THEN
TUS.SalesStageProbability ELSE TU.SalesStageProbability END AS
SalesStageProbability, CASE WHEN TU.OpportunityType IS NULL THEN
TUS.OpportunityType ELSE TU.OpportunityType END AS OpportunityType, CASE
WHEN TU.OpportunityClosedDate IS NULL THEN TUS.OpportunityClosedDate ELSE
TU.OpportunityClosedDate END AS OpportunityClosedDate, CASE WHEN
TU.OpportunityClosedFY IS NULL THEN TUS.OpportunityClosedFY ELSE
TU.OpportunityClosedFY END AS OpportunityClosedFY, CASE WHEN
TU.OpportunityClosedFM IS NULL THEN TUS.OpportunityClosedFM ELSE
TU.OpportunityClosedFM END AS OpportunityClosedFM, CASE WHEN
TU.OpportunityDueDate IS NULL THEN TUS.OpportunityDueDate ELSE
TU.OpportunityDueDate END AS OpportunityDueDate, CASE WHEN
TU.OpportunityDueFY IS NULL THEN TUS.OpportunityDueFY ELSE
TU.OpportunityDueFY END AS OpportunityDueFY, CASE WHEN TU.OpportunityDueFM
IS NULL THEN TUS.OpportunityDueFM ELSE TU.OpportunityDueFM END AS
OpportunityDueFM, CASE WHEN TU.OpportunityDueFQ IS NULL THEN
TUS.OpportunityDueFQ ELSE TU.OpportunityDueFQ END AS OpportunityDueFQ,
CASE WHEN TU.OpportunityClosedFQ IS NULL THEN TUS.OpportunityClosedFQ ELSE
TU.OpportunityClosedFQ END AS OpportunityClosedFQ,
join: FROM
dbo.STG_Accounts AS A LEFT OUTER JOIN dbo.STG_Windows8TabletUnits_Actuals
AS TU ON A.CRMID = TU.CRMID LEFT OUTER JOIN
dbo.STG_Windows8TabletUnitsShipped_Actuals AS TUS ON TU.CRMOpportunityID =
TUS.CRMOpportunityID OR A.CRMID = TUS.CRMID AND TU.CRMOpportunityID IS
NULL
No comments:
Post a Comment