/* Выборка списка населенных пунктов, для которых в БД есть объекты недвижимости */
ALTER PROCEDURE [dbo].[realty_GetExistSettlement]
(
	@ParentCode nvarchar(13),
	@ID_ObjectType int
)
AS

-- процедура принимает код уровня КЛАДР и выбирает из таблицы объектов всех потомков переданного уровня, объекта заданной категории 
-- выборка делается из таблицы КЛАДР
-- Сначала определяем уровень переданного кода

-- зная уровень выберим из первых четырех уровней все н/п у которых код LIKE переданный_код_без_конечных_нулей + '%'
	DECLARE @Code nvarchar(13)
	DECLARE @Code1 nvarchar(13)
	SET @Code = @ParentCode
	SET @Code1 = @Code
	-- убрать в конце нули
	IF SUBSTRING(@Code1, 9, 3) = '000'
	BEGIN
		SET @Code1 = LEFT(@Code1, 8)

		IF SUBSTRING(@Code1, 6, 3) = '000'
		BEGIN
			SET @Code1 = LEFT(@Code1, 5)

				IF SUBSTRING(@Code1, 3, 3) = '000'
				BEGIN
					SET @Code1 = LEFT(@Code1, 2)
				END
		END
	END

	
	DECLARE @RankTable TABLE
	(
		Rank int identity not null,
		Code nvarchar(13),
		[Name] nvarchar(40),
		ID_Object int
	);

	IF @ID_ObjectType = 1
	BEGIN
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel4Code, AddressLevel4, ID_Building FROM realty_Building WHERE 
		AddressLevel4Code LIKE @Code1 + '%' 
		AND AddressLevel4Code <> @Code

	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel3Code, AddressLevel3, ID_Building FROM realty_Building WHERE 
		AddressLevel3Code LIKE @Code1 + '%' 
		AND AddressLevel3Code <> @Code
		AND ID_Building NOT IN (SELECT ID_Object FROM @RankTable)
	
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel2Code, AddressLevel2, ID_Building FROM realty_Building WHERE 
		AddressLevel2Code LIKE @Code1 + '%' 
		AND AddressLevel2Code <> @Code
		AND ID_Building NOT IN (SELECT ID_Object FROM @RankTable)
		
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel4Code, AddressLevel4, ID_Building FROM realty_Building WHERE 
		(AddressLevel2Code LIKE @Code1 + '%' OR AddressLevel3Code LIKE @Code1 + '%')
		AND AddressLevel4Code IS NULL
		AND AddressLevel4 IS NOT NULL
		AND ID_Building NOT IN (SELECT ID_Object FROM @RankTable)
		AND AbsentAddress = 1
	END
	ELSE
	BEGIN
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel4Code, AddressLevel4, ID_Object FROM realty_Object WHERE 
		AddressLevel4Code LIKE @Code1 + '%' 
		AND AddressLevel4Code <> @Code
		AND ID_ObjectType = @ID_ObjectType

	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel3Code, AddressLevel3, ID_Object FROM realty_Object WHERE 
		AddressLevel3Code LIKE @Code1 + '%' 
		AND AddressLevel3Code <> @Code
		AND ID_Object NOT IN (SELECT ID_Object FROM @RankTable)
		AND ID_ObjectType = @ID_ObjectType
	
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel2Code, AddressLevel2, ID_Object FROM realty_Object WHERE 
		AddressLevel2Code LIKE @Code1 + '%' 
		AND AddressLevel2Code <> @Code
		AND ID_Object NOT IN (SELECT ID_Object FROM @RankTable)
		AND ID_ObjectType = @ID_ObjectType
		
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel4Code, AddressLevel4, ID_Object FROM realty_Object WHERE 
		(AddressLevel2Code LIKE @Code1 + '%' OR AddressLevel3Code LIKE @Code1 + '%')
		AND AddressLevel4Code IS NULL
		AND AddressLevel4 IS NOT NULL
		AND ID_Object NOT IN (SELECT ID_Object FROM @RankTable)
		AND ID_ObjectType = @ID_ObjectType
		AND AbsentAddress = 1
	END

	SELECT DISTINCT
		r.Code,
		r.Name + ' (' + CAST((SELECT Count([Name]) FROM @RankTable WHERE [Name] = r.[Name]) as nvarchar(30)) + ')' as [Name],
		r.Name as CleanName
	FROM @RankTable as r
	ORDER BY [Name]

/*
Missing Index Details from SQLQuery1.sql
The Query Processor estimates that implementing the following index could improve the query cost by 76.0531%.
*/


USE [YourDBName]
GO
CREATE NONCLUSTERED INDEX [AddrLevel4Index]
ON [dbo].[realty_Object] ([ID_ObjectType],[AddressLevel4Code])
INCLUDE ([ID_Object],[AddressLevel4])
GO