WHERE NOT EXISTS (SELECT 1
FROM [OCC].[dbo].[OrgGeneralInfo] AS OrgGen
--Inner join to include last valid row per key
INNER JOIN (SELECT [OrganisationID]
, MAX(ValidFromDate) MAXValidFromDate
FROM OCC.dbo.ORGGeneralInfo
GROUP BY [OrganisationID] ) AS ORGMax
ON ORGMax.OrganisationID = OrgGen.OrganisationID
AND ORGMax.MAXValidFromDate = OrgGen.ValidFromDate
WHERE OrgGen.[OrganisationID] = Org.[OrganisationID]
AND OrgGen.[KknrID] = KR.[KknrID]
AND OrgGen.[OrganisationNo] = R1.[OrgNo]
AND OrgGen.[Name] = R1.[OrgName]
AND OrgGen.[CustomerSegmentNKey] =
R1.[CustomerSegmentNKey]
AND OrgGen.[CustomerSegment] = R1.[CustomerSegment]
AND OrgGen.[SectorNKey] = R1.[SectorNKey]
AND OrgGen.[Sector] = R1.[Sector]
AND OrgGen.[SalesUnitNKey] = R1.[SalesUnitNKey]
AND OrgGen.[SalesUnit] = R1.[SalesUnit]
AND OrgGen.[SalesTeamNKey] = R1.[SalesTeamNKey]
AND OrgGen.[SalesTeam] = R1.[SalesTeam]
AND OrgGen.[SalesPersonNKey] = R1.[SalesPersonNKey]
AND OrgGen.[SalesPerson] = R1.[SalesPerson]
AND OrgGen.[SegmentNKey] = R1.[SegmentNKey]
AND OrgGen.[Segment] = R1.[Segment]
AND OrgGen.[IndustryNKey] = R1.[IndustryNKey]
AND OrgGen.[Industry] = R1.[Industry]
AND OrgGen.[AccountTypeNKey] = R1.[AccountTypeNKey]
AND OrgGen.[AccountType] = R1.[AccountType]
AND ISNULL(OrgGen.[CustomerNKey],-99) = ISNULL(ISNULL(AC.CustomerNKey,ORGGeneral.CustomerNKey),-99)
--and (OrgGen.[CustomerNKey] IS NULL AND AC.CustomerNKey IS NULL AND ORGGeneral.CustomerNKey IS NULL)
-- and OrgGen.CustomerNKey = AC.CustomerNKey
-- OR
-- OrgGen.CustomerNKey = ORGGeneral.CustomerNKey
-- OR
-- (
-- OrgGen.CustomerNKey IS NULL
-- AND AC.CustomerNKey IS NULL
-- AND ORGGeneral.CustomerNKey
--)
-- AND ISNULL(OrgGen.[CustomerNKey],-99) =(OrgGen.[CustomerNKey] IS NULL AND AC.CustomerNKey IS NULL AND ORGGeneral.CustomerNKey IS NULL)
)
AND LEN(R1.ORGNAME)>0need optimisation