Drop Table ##TT

Select DuplC.ID As ContractID, DuplC.BudgetHierarchyID As ContractBudgetHierarchyID, DuplC.FinancingAgentDepartmentID, DuplCF.ContractOrganizationID, DuplF.OLD_ID As FinancingItemOldID
	Into ##TT
From dbo.BL_Contracts DuplC
Left Join dbo.BL_ContractOrganizations DuplOrg On DuplOrg.ContractID = DuplC.ID
Left Join dbo.BL_ContractFinancingItems DuplCF On DuplCF.ContractOrganizationID = DuplOrg.ID
Left Join dbo.BL_FinancingItems DuplF On DuplF.ID = DuplCF.FinancingItemID And DuplC.BudgetHierarchyID != DuplF.BudgetHierarchyID
Where DuplOrg.DateDeleted Is Null
		And DuplCF.DateDeleted = '2014-09-18 15:00:00.000'
		And DuplF.StatusID = '36514995-B274-4BB4-9158-0F4C466CF07B'

Select Distinct
	D.ContractID , D.ContractBudgetHierarchyID , D.FinancingAgentDepartmentID , D.ContractOrganizationID ,
	NewT.FinancingItemID As NewFinancingItemID, NewT.FinancingItemOldID As NewFinancingItemOldID, NewT.BudgetHierarchyID As NewFinancingItemBudgetHierarchyID, NewT.ArtificialCode As NewArtificialCode, NewT.MaxUnitCost As NewMaxUnitCost,
	DuplT.FinancingItemID As DuplFinancingItemID, DuplT.FinancingItemOldID As DuplFinancingItemOldID, DuplT.BudgetHierarchyID As DuplBudgetHierarchyID, DuplT.ArtificialCode As DuplArtificialCode, DuplT.MaxUnitCost As DuplMaxUnitCost
From ##TT D

Left Join (
				Select NewC.ID As ContractID, NewF.BudgetHierarchyID, NewCF.ContractOrganizationID, NewF.OLD_ID As FinancingItemOldID, NewF.ArtificialCode, NewCF.FinancingItemID, NewCF.MaxUnitCost
				From dbo.BL_Contracts NewC
				Left Join dbo.BL_ContractOrganizations NewOrg On NewOrg.ContractID = NewC.ID
				Left Join dbo.BL_ContractFinancingItems NewCF On NewCF.ContractOrganizationID = NewOrg.ID
				Left Join dbo.BL_FinancingItems NewF On NewF.ID = NewCF.FinancingItemID And NewC.BudgetHierarchyID = NewF.BudgetHierarchyID
				Where NewOrg.DateDeleted Is Null
					And NewCF.DateDeleted Is Null
					And NewF.StatusID = '36514995-B274-4BB4-9158-0F4C466CF07B'
					And Exists(Select * From ##TT T Where T.ContractID = NewOrg.ContractID And T.ContractOrganizationID =  NewCF.ContractOrganizationID And T.FinancingItemOldID = NewF.OLD_ID )
				--Order By ContractID, BudgetHierarchyID, FinancingItemOldID
		) NewT On D.ContractID = NewT.ContractID And NewT.ContractOrganizationID = D.ContractOrganizationID And D.FinancingItemOldID = NewT.FinancingItemOldID 

Left Join (
				Select DuplC.ID As ContractID, DuplF.BudgetHierarchyID, DuplCF.ContractOrganizationID, DuplF.OLD_ID As FinancingItemOldID, DuplF.ArtificialCode, DuplCF.FinancingItemID, DuplCF.MaxUnitCost
				From dbo.BL_Contracts DuplC
				Left Join dbo.BL_ContractOrganizations DuplOrg On DuplOrg.ContractID = DuplC.ID
				Left Join dbo.BL_ContractFinancingItems DuplCF On DuplCF.ContractOrganizationID = DuplOrg.ID
				Left Join dbo.BL_FinancingItems DuplF On DuplF.ID = DuplCF.FinancingItemID And DuplC.BudgetHierarchyID != DuplF.BudgetHierarchyID
				Where DuplOrg.DateDeleted Is Null
						And DuplCF.DateDeleted = '2014-09-18 15:00:00.000'
						And DuplF.StatusID = '36514995-B274-4BB4-9158-0F4C466CF07B'
						And Exists(Select * From ##TT T Where T.ContractID = DuplOrg.ContractID And T.ContractOrganizationID = DuplCF.ContractOrganizationID And T.FinancingItemOldID = DuplF.OLD_ID )
		) DuplT On D.ContractID = DuplT.ContractID And DuplT.ContractOrganizationID = D.ContractOrganizationID And D.FinancingItemOldID = DuplT.FinancingItemOldID 
