CREATE VIEW [dbo].[VwExtrimistRiskOperation]

as
	
	
with HeightenedContact (contactId) as (
	select ero.ContactId
	from ExtrimistRiskOperation ero
	where ero.RiskLevelHeightenedValue = 1 
				and ero.CreatedOn IS NOT NULL 
				and ero.ModifiedOn > cast('7/17/2018' as datetime)
	group by ero.ContactId
),
ContactWithRisk (keyColl, contactId, riskId) as (
	select 2, hc.contactId, ero1.Id
	from HeightenedContact hc
		outer apply (
			select TOP 1 tempEro.Id
			FROM ExtrimistRiskOperation tempEro
			WHERE tempEro.ContactId = hc.contactId and tempEro.RiskLevelHeightenedValue = 1
			ORDER BY tempEro.IsAddedManually desc, tempEro.ModifiedOn desc
		) ero1

	union

	select 1, c.Id, ero2.Id
	from Contact c
		join ExtrimistRiskOperation ero on c.Id = ero.ContactId
		outer apply (
			select TOP 1 tempEro.Id
			FROM ExtrimistRiskOperation tempEro 
			WHERE tempEro.ContactId = c.Id
			ORDER BY tempEro.ModifiedOn desc 
		) ero2
	where c.Id not in (select contactId from  HeightenedContact)
		and ero.RiskLevelStandardValue = 1 
					and ero.CreatedOn IS NOT NULL 
					and ero.ModifiedOn > cast('7/17/2018' as datetime)
) 

--дальше идёт селект сборки, к которому особо вопросов нет.

create function dbo.fn_getExtrimistRiskOperationAndContact() 
	returns @contact table (idContact uniqueidentifier, idOperation uniqueidentifier, isHigh bit)
	as
	begin

		insert into @contact
		select ero.ContactId, ero.Id, 0
		from (
			select tempEro.ContactId, MAX(tempEro.ModifiedOn) as ModifiedOn
			from ExtrimistRiskOperation tempEro
			group by tempEro.ContactId
		) eroReq
			join ExtrimistRiskOperation ero on eroReq.ContactId = ero.ContactId and eroReq.ModifiedOn = ero.ModifiedOn

		update @contact
		set isHigh = 1
		from ExtrimistRiskOperation ero 
			join @contact c on ero.ContactId = c.idContact
		where ero.RiskLevelHeightenedValue = 1
			and ero.ModifiedOn > cast('7/17/2018' as datetime) 
			and ero.CreatedOn IS NOT NULL 

		update @contact
		set idOperation = ero.Id
		from @contact c
			outer apply (
				select TOP 1 tempEro.Id
				FROM ExtrimistRiskOperation tempEro
				WHERE tempEro.ContactId = c.idContact and tempEro.RiskLevelHeightenedValue = 1
				ORDER BY tempEro.IsAddedManually desc, tempEro.ModifiedOn desc
		) ero
		where isHigh = 1

		return
	end	

with c as ( select * from dbo.Contact ) 
   , e as ( select * from dbo.ExtrimistRiskOperation )
   , ContactWithRisk as (
	    select keyColl = iif( e.xRiskLevelHeightenedValue = 1, 2, 1)
             , c.*
             , e.*
	    from c
		    outer apply ( select top(1) *,  xRiskLevelHeightenedValue = iif( e.CreatedOn is not null and ero.ModifiedOn > '20180717', e.RiskLevelHeightenedValue, 0 )
                           FROM e WHERE e.ContactId = c.Id 
                            ORDER BY xRiskLevelHeightenedValue desc
                                   , e.ModifiedOn desc 
                        ) e
     )
   select * from ContactWithRisk
   -- ежели xRiskLevelHeightenedValue материализовать - будет быстрее

alter table ExtrimistRiskOperation add isHigh as cast(case when RiskLevelHeightenedValue = 1 and IsAddedManually = 1 and ModifiedOn > '20180717' then 1 else 0 end as bit);

create index IX_ExtrimistRiskOperation__isHigh on ExtrimistRiskOperation (ContractId, IsHigh desc, ModifiedOn desc) include (Id) where CreatedOn is not null;

create function dbo.fn_getExtrimistRiskOperationAndContact() 
returns table
as
return (
 with s as
 (
  select
   ContractId, Id, IsHigh,
   row_number() over (partition by ContractId order by IsHigh desc, ModifiedOn desc) as rn
  from
   ExtrimistRiskOperation
  where
   CreatedOn is not null
 )
 select
  ContractId, Id, IsHigh
 from
  s
 where
  rn = 1
);