question

-- Use NOLOCK everywhere
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- ###################################################
-- Declare and set variables
-- ###################################################

DECLARE @CurrBudScen INT;
DECLARE @PrevBudScen INT;
DECLARE @OutputMsg VARCHAR(200);
DECLARE @AOData TABLE
			(AccountID INT NOT NULL
			,LeaseID INT NOT NULL
			,ScenarioID INT NOT NULL
			,PeriodID INT NOT NULL
			,Value DECIMAL(12,2) NOT NULL);

SET @CurrBudScen = 6410;
SET @PrevBudScen = 4757;

-- ###################################################
-- Get Data from AccountOutput
-- ###################################################

-- Current Budget
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SET @OutputMsg =  CONVERT(VARCHAR(30), GETDATE(), 8) + ' - Get Data from AccountOutput: Current Budget'
PRINT @OutputMsg

INSERT INTO @AOData

	SELECT
		ao.AccountID
		,ao.EntityID AS [LeaseID]
		,ao.ScenarioID
		,ao.PeriodID
		,ao.Value
		
	FROM AccountOutput ao

	WHERE
		ao.AccountID IN (82,83) -- New and Previous Rent on Rent Review
		AND ao.ScenarioID = @CurrBudScen
		AND ao.IsComparisonCopy = 0
		AND ao.IsRefScenario = 0;

-- (3431 row(s) affected)

-- Previous Budget
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SET @OutputMsg =  CONVERT(VARCHAR(30), GETDATE(), 8) + ' - Get Data from AccountOutput: Previous Budget'
PRINT @OutputMsg

INSERT INTO @AOData

	SELECT
		ao.AccountID
		,ao.EntityID AS [LeaseID]
		,ao.ScenarioID
		,ao.PeriodID
		,ao.Value
		
	FROM AccountOutput ao

	WHERE
		ao.AccountID IN (82,83) -- New and Previous Rent on Rent Review
		AND ao.ScenarioID = @PrevBudScen
		AND ao.IsComparisonCopy = 0
		AND ao.IsRefScenario = 0;

-- (3549 row(s) affected)

-- Remove Orphaned Data
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SET @OutputMsg =  CONVERT(VARCHAR(30), GETDATE(), 8) + ' - Get Data from AccountOutput: Remove Orphaned Data'
PRINT @OutputMsg

DELETE aod

FROM @AOData aod

	LEFT JOIN Lease l ON
		l.LeaseID = aod.LeaseID
	
	LEFT JOIN LeaseScenarioDetail lsd ON
		lsd.LeaseID = l.LeaseID
		AND lsd.ScenarioID = aod.ScenarioID

	LEFT JOIN Asset a ON
		a.AssetID = l.AssetID
	
	LEFT JOIN AssetScenarioDetail asd ON
		asd.AssetID = a.AssetID
		AND asd.ScenarioID = aod.ScenarioID

	LEFT JOIN [Site] s ON
		s.SiteID = a.SiteID
	
	LEFT JOIN SiteScenarioDetail ssd ON
		ssd.SiteID = s.SiteID
		AND ssd.ScenarioID = aod.ScenarioID

WHERE
	lsd.RecordStatus = 316
	OR asd.RecordStatus = 316
	OR ssd.RecordStatus = 316
	OR lsd.RecordStatus IS NULL
	OR asd.RecordStatus IS NULL
	OR ssd.RecordStatus IS NULL;

-- (498 row(s) affected)

-- ###################################################
-- CTEs
-- ###################################################

-- Move new rent on review and previous rent on review
-- into columns on th same rwo for current budget 
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

WITH CurrBudgetData AS

	(SELECT
		aod1.LeaseID
		,aod1.PeriodID
		,aod1.Value AS [NewRentOnReview]
		,aod2.Value AS [PrevRentOnReview]
		
	FROM @AOData aod1
		
		INNER JOIN @AOData aod2 ON
			aod2.LeaseID = aod1.LeaseID
			AND aod2.ScenarioID = aod1.ScenarioID
			AND aod2.PeriodID = aod1.PeriodID
			and aod2.AccountID = 83 --Previous Rent on Rent Review
	WHERE
		aod1.AccountID = 82 --New Rent on Rent Review
		AND aod1.ScenarioID = @CurrBudScen)

-- Move new rent on review and previous rent on review
-- into columns on th same rwo for previous budget 
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

,PrevBudgetData AS

	(SELECT
		aod1.LeaseID
		,aod1.PeriodID
		,aod1.Value AS [NewRentOnReview]
		,aod2.Value AS [PrevRentOnReview]
		
	FROM @AOData aod1
		
		INNER JOIN @AOData aod2 ON
			aod2.LeaseID = aod1.LeaseID
			AND aod2.ScenarioID = aod1.ScenarioID
			AND aod2.PeriodID = aod1.PeriodID
			and aod2.AccountID = 83 --Previous Rent on Rent Review
	WHERE
		aod1.AccountID = 82 --New Rent on Rent Review
		AND aod1.ScenarioID = @PrevBudScen)

-- Create map between current budget and previous budget
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Match rent reviews in same month under same SiteID 

,CurrToPrevMAP1 AS

	(SELECT
		cbd.LeaseID AS [CBLeaseID]
		,pbd.LeaseID AS [PBLeaseID]
		,cbd.PeriodID AS [PeriodID]
		
	FROM CurrBudgetData cbd
		
		INNER JOIN Lease lc ON
			lc.LeaseID = cbd.LeaseID

		INNER JOIN Asset ac ON
			ac.AssetID = lc.AssetID
		
		INNER JOIN Asset ap ON
			ap.SiteID = ac.SiteID
		
		INNER JOIN Lease lp ON
			lp.AssetID = ap.AssetID
		
		INNER JOIN PrevBudgetData pbd ON
			pbd.LeaseID = lp.LeaseID
			AND pbd.PeriodID = cbd.PeriodID)

-- Pick from first list those matches with same LeaseID 

,CurrToPrevMAP2 AS

	(SELECT
		ctpm.CBLeaseID
		,ctpm.PBLeaseID
		,ctpm.PeriodID
		
	FROM CurrToPrevMAP1 ctpm
	
	WHERE ctpm.CBLeaseID = ctpm.PBLeaseID)

-- Pick from first list those matches with different LeaseID
-- that also don't have a same leaseID match (this will be demmed 
-- to over-rule the non-matching LeaseID initial match) 

,CurrToPrevMAP3 AS

	(SELECT
		ctpm.CBLeaseID
		,ctpm.PBLeaseID
		,ctpm.PeriodID
		
	FROM CurrToPrevMAP1 ctpm
	
	WHERE
		ctpm.CBLeaseID <> ctpm.PBLeaseID
		AND ctpm.CBLeaseID NOT IN (SELECT CBLeaseID FROM CurrToPrevMAP2))

-- Aggregate two sub-selections above
-- i.e. remove non-matching LeaseID initial matches where same
-- LeaseID matches also found under the same site

,CurrToPrevMAP4 AS

	(SELECT * FROM CurrToPrevMAP2
		
	UNION ALL
	
	SELECT * FROM CurrToPrevMAP3)

-- Get list of all insatnces of unique CBLeaseID & PeriodID combos

,CurrToPrevMAP5 AS
	
	(SELECT DISTINCT CBLeaseID, PeriodID
	FROM CurrToPrevMAP4 AS A
	WHERE
        (SELECT COUNT(CBLeaseID) 
         FROM CurrToPrevMAP4 AS B
         WHERE B.CBLeaseID = A.CBLeaseID
         AND B.PeriodID = A.PeriodID) = 1)

-- Use above unique list to NULL out any residual mutliple
-- matches on the grounds that these are too difficult 
-- to programatically match 

,CurrToPrevMAP6 AS
	
	(SELECT DISTINCT
		ctpm4.CBLeaseID
		,CASE WHEN ctpm5.CBLeaseID IS NULL THEN NULL ELSE ctpm4.PBLeaseID END AS [PBLeaseID]
		,ctpm4.PeriodID
	FROM CurrToPrevMAP4 ctpm4
		LEFT JOIN CurrToPrevMAP5 ctpm5 ON
			ctpm5.CBLeaseID = ctpm4.CBLeaseID
			AND ctpm5.PeriodID = ctpm4.PeriodID)     

-- Get distinct Lease, PeriodID list from current budget

,CurrToPrevMAP7 AS

	(SELECT DISTINCT
		cbd.LeaseID AS [CBLeaseID]
		,cbd.PeriodID AS [PeriodID]
		
	FROM CurrBudgetData cbd)

-- Final Map!!!
-- Left Join unique list from current budget to mapped IDs for
-- final map

,CurrToPrevMAP AS

	(SELECT
		ctpm7.CBLeaseID
		,ctpm6.PBLeaseID
		,ctpm7.PeriodID
		
	FROM CurrToPrevMAP7 ctpm7
	
		LEFT JOIN CurrToPrevMAP6 ctpm6 ON
			ctpm7.CBLeaseID = ctpm6.CBLeaseID
			AND ctpm7.PeriodID = ctpm6.PeriodID)
			
-- ###################################################
-- Main Query
-- ###################################################

-- takes 45 seconds
SELECT * FROM CurrToPrevMAP6;

-- takes 25 seconds
SELECT * FROM CurrToPrevMAP7;

-- takes over two hours and counting?
SELECT * FROM CurrToPrevMAP;

-- Reset default LOCK behaviour
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;