ALTER TABLE [AKUZ].[T_PATIENT_ADDRESS] ADD [CalcAddress] varchar(2000) NULL
GO

UPDATE PA
	set 
		pa.CalcAddress = CASE WHEN PA.City is null and PA.Street is null then PA.FreeAddressString
							ELSE
							CONCAT(COALESCE(C.ZIPCode + ' ', ''),
									CASE WHEN RE.RegionType IS NOT NULL AND RE.RegionType != '' THEN RE.RegionType ELSE '' END,
									CASE WHEN RE.Name IS NOT NULL AND RE.Name != '' THEN '.' + RE.Name ELSE '' END,
									CASE WHEN R.Name IS NOT NULL AND R.Name != '' THEN ', נאימם ' + R.Name + ', ' ELSE '' END,
									CASE WHEN C.CityType IS NOT NULL AND C.CityType != ', ' THEN C.CityType ELSE '' END,
									CASE WHEN C.Name IS NOT NULL AND C.Name != '' THEN '.' + C.Name ELSE '' END,
									CASE WHEN ST.Name IS NOT NULL AND ST.Name != '' THEN ', ' + ST.Name ELSE '' END,
									CASE WHEN PA.House IS NOT NULL AND PA.House != '' AND PA.House != '0' THEN ', ' + PA.House ELSE '' END,
									CASE WHEN PA.Block IS NOT NULL AND PA.Block != '' AND PA.Block != '0' THEN ', ' + PA.Block ELSE '' END,
									CASE WHEN PA.Flat IS NOT NULL AND PA.Flat != '' AND PA.Flat != '0' THEN ', ' + PA.Flat ELSE '' END) 
							END
FROM AKUZ.T_PATIENT_ADDRESS	PA (NOLOCK)
	LEFT JOIN AKUZDB.AKUZ.T_CITY C (NOLOCK) ON C.CityID = PA.City
	LEFT JOIN AKUZDB.AKUZ.T_STREET ST (NOLOCK) ON ST.StreetID = PA.Street
	LEFT JOIN AKUZDB.AKUZ.T_RAJON R (NOLOCK) ON R.RajonID = C.Rajon
	LEFT JOIN AKUZDB.AKUZ.T_REGION AS RE (NOLOCK) ON RE.RegionID = R.Region
WHERE PA.CalcAddress is null
GO

if not exists(SELECT * FROM sys.fulltext_indexes fti where fti.[object_id] = OBJECT_ID('AKUZ.T_PATIENT_ADDRESS', 'U'))
	CREATE FULLTEXT INDEX ON AKUZ.T_PATIENT_ADDRESS(CalcAddress) KEY INDEX PK_T_PATIENT_ADDRESS ON (FullTextCatalog) WITH (CHANGE_TRACKING AUTO);
