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