Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
Добрый день, форумчане.

У меня беда с которой я воюю уже четвертый день с утра и до самого вечера и результата нет.
Ниже изображена таблица из которой я потом "конкатом" собираю текст в отчеты.
Таблица собирается из разных таблиц, но самое главное- это чтобы интервалы были отсортированы и каждый "на своем месте", то есть если у первой таблице отметка 20, то отметка второй таблицы должна быть тоже 20, а если там 22, то эти данные должны быть ниже, но у меня никак не получается этого достичь. По какой причине я не пойму, использовала всевозможные сортировки и связи - результата нет.

Вот на всякий случай скрипт, хотя от него толку ноль... Мне бы идею, как это сделать. Подскажите, слезно прошу, поЖЖЖалуйста
+
-- Type your SQL script here.
    select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid, 
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,[a].Primary_Lithology_rus_Desc_D order by a.geolfrom) when 1 then [a].Primary_Lithology_rus_Desc_D end as 'Primary Lithology',



geolfrom_sec,geolto_sec,Lith3_RockName,rn_sec,geolfrom_str, geolto_str, Strucrure,rn_struc, geolfrom_alt,geolto_alt,Alteration,rn_alt,geolfrom_min,geolto_min,Mineralisation, rn_min from

( SELECT  TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Lith_RockName_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_RockName_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Lith1_pct]) is null THEN '' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith1_pct])) + '%' + ')'
END + 
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith_Colour_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Texture_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Structure_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Structure_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_RockName_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_RockName_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Lith2_pct]) is null THEN '' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith2_pct])) + '%' + ')'
END + 
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Colour_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Colour_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Texture_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Structure_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Comments] is null THEN '' ELSE ', ( ' + [ACQDERIVEDVIEW].[Lith_Comments] + ')'
END
 )  AS [Primary_Lithology_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Lith_CompRock_rus_Desc_D] is null THEN '' ELSE  [ACQDERIVEDVIEW].[Lith_CompRock_rus_Desc_D]+': '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_RockName_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_RockName] + ' - ' + [ACQDERIVEDVIEW].[Lith_RockName_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_pct] = 100 THEN ',' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith1_pct])) + '%' + '),'
END + 
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour_Int_rus_Desc_D] is null THEN '' ELSE  ' ' +  [ACQDERIVEDVIEW].[Lith_Colour_Int_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour_rus_Desc_D] is null THEN '' ELSE ' ' +  [ACQDERIVEDVIEW].[Lith_Colour_rus_Desc_D] + ' цвет, '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour2_Int_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_Colour2_Int_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour2_rus_Desc_D] is null THEN '' ELSE  [ACQDERIVEDVIEW].[Lith_Colour2_rus_Desc_D] + ' - второстепенный цвет '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture_rus_Desc_D] is null THEN '' ELSE ', структура ' + [ACQDERIVEDVIEW].[Lith1_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Texture2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Structure_rus_Desc_D] is null THEN '' ELSE ', текстура ' + [ACQDERIVEDVIEW].[Lith1_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Structure2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Structure2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_RockName_rus_Desc_D] is null THEN '' ELSE '; ' + [ACQDERIVEDVIEW].[Lith2_RockName] + ' - ' + [ACQDERIVEDVIEW].[Lith2_RockName_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Lith2_pct]) is null THEN '' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith2_pct])) + '%' + ')'
END + 
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Colour_rus_Desc_D] is null THEN '' ELSE ', цвет ' + [ACQDERIVEDVIEW].[Lith2_Colour_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture_rus_Desc_D] is null THEN '' ELSE ', структура ' + [ACQDERIVEDVIEW].[Lith2_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Texture2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure_rus_Desc_D] is null THEN '' ELSE ', текстура ' + [ACQDERIVEDVIEW].[Lith2_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Comments] is null THEN '' ELSE ', (Прим. ' + [ACQDERIVEDVIEW].[Lith_Comments] + ')'
END  )  AS [Primary_Lithology_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour'
and LOOKUP = [DFINDF].[Lith_Colour]
and ACTIVE = 1

 )  AS [Lith_Colour_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_RockName'
and LOOKUP = [DFINDF].[Lith_RockName]
and ACTIVE = 1 )  AS [Lith_RockName_Desc_D],  ( select LOWER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Structure'
and LOOKUP = [DFINDF].[Lith1_Structure]
and ACTIVE = 1 )  AS [Lith1_Structure_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Texture'
and LOOKUP = [DFINDF].[Lith1_Texture]
and ACTIVE = 1 )  AS [Lith1_Texture_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Colour'
and LOOKUP = [DFINDF].[Lith2_Colour]
and ACTIVE = 1 )  AS [Lith2_Colour_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_RockName'
and LOOKUP = [DFINDF].[Lith2_RockName]
and ACTIVE = 1 )  AS [Lith2_RockName_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Structure'
and LOOKUP = [DFINDF].[Lith2_Structure]
and ACTIVE = 1 )  AS [Lith2_Structure_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Texture'
and LOOKUP = [DFINDF].[Lith2_Texture]
and ACTIVE = 1 )  AS [Lith2_Texture_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+UPPER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_CompRock'
and LOOKUP = [DFINDF].[Lith_CompRock]
and ACTIVE = 1 )  AS [Lith_CompRock_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_RockName'
and LOOKUP = [DFINDF].[Lith_RockName]
and ACTIVE = 1
 )  AS [Lith_RockName_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour_Int'
and LOOKUP = [DFINDF].[Lith_Colour_Int]
and ACTIVE = 1

 )  AS [Lith_Colour_Int_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour'
and LOOKUP = [DFINDF].[Lith_Colour]
and ACTIVE = 1

 )  AS [Lith_Colour_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour2_Int'
and LOOKUP = [DFINDF].[Lith_Colour2_Int]
and ACTIVE = 1

 )  AS [Lith_Colour2_Int_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour2'
and LOOKUP = [DFINDF].[Lith_Colour2]
and ACTIVE = 1

 )  AS [Lith_Colour2_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Texture'
and LOOKUP = [DFINDF].[Lith1_Texture]
and ACTIVE = 1 )  AS [Lith1_Texture_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Texture2'
and LOOKUP = [DFINDF].[Lith1_Texture2]
and ACTIVE = 1 )  AS [Lith1_Texture2_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Structure'
and LOOKUP = [DFINDF].[Lith1_Structure]
and ACTIVE = 1 )  AS [Lith1_Structure_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Structure2'
and LOOKUP = [DFINDF].[Lith1_Structure2]
and ACTIVE = 1 )  AS [Lith1_Structure2_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_RockName'
and LOOKUP = [DFINDF].[Lith2_RockName]
and ACTIVE = 1 )  AS [Lith2_RockName_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Colour'
and LOOKUP = [DFINDF].[Lith2_Colour]
and ACTIVE = 1 )  AS [Lith2_Colour_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Texture'
and LOOKUP = [DFINDF].[Lith2_Texture]
and ACTIVE = 1 )  AS [Lith2_Texture_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Texture2'
and LOOKUP = [DFINDF].[Lith2_Texture2]
and ACTIVE = 1 )  AS [Lith2_Texture2_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Structure'
and LOOKUP = [DFINDF].[Lith2_Structure]
and ACTIVE = 1 )  AS [Lith2_Structure_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Structure2'
and LOOKUP = [DFINDF].[Lith2_Structure2]
and ACTIVE = 1 )  AS [Lith2_Structure2_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Lith_Comments], [Lith1_pct], [Lith2_pct], [Lith_RockName], [Lith2_RockName] 
FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], [GEODETAILS].[Lith_Colour], 
[GEOBIGCOMMENT].[Lith_Comments], [GEODETAILS].[Lith_RockName], CAST([GEODETAILS].[Lith1_pct] AS FLOAT) AS [Lith1_pct], [GEODETAILS].[Lith1_Structure], [GEODETAILS].[Lith1_Texture],
 [GEODETAILS].[Lith2_Colour], CAST([GEODETAILS].[Lith2_pct] AS FLOAT) AS [Lith2_pct], [GEODETAILS].[Lith2_RockName], [GEODETAILS].[Lith2_Structure], [GEODETAILS].[Lith2_Texture], 
 [GEODETAILS].[Lith_CompRock], [GEODETAILS].[Lith_Colour_Int], [GEODETAILS].[Lith_Colour2_Int], [GEODETAILS].[Lith_Colour2], [GEODETAILS].[Lith1_Texture2], [GEODETAILS].[Lith1_Structure2],
  [GEODETAILS].[Lith2_Texture2], [GEODETAILS].[Lith2_Structure2] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID]
    AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM],
	 [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour], min(CASE when [GEODETAILS].[NAME] = 'Lith_RockName' 
	 then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_RockName], min(CASE when [GEODETAILS].[NAME] = 'Lith1_pct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Lith1_pct], min(CASE when [GEODETAILS].[NAME] = 'Lith1_Structure' 
	 then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Structure], min(CASE when [GEODETAILS].[NAME] = 'Lith1_Texture' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Texture], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Colour' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Colour], 
min(CASE when [GEODETAILS].[NAME] = 'Lith2_pct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Lith2_pct], min(CASE when [GEODETAILS].[NAME] = 'Lith2_RockName' 
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_RockName], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Structure' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Structure], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Texture' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Texture],
 min(CASE when [GEODETAILS].[NAME] = 'Lith_CompRock' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_CompRock], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour_Int' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour_Int], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour2_Int'
  then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour2_Int], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour2], min(CASE when [GEODETAILS].[NAME] = 'Lith1_Texture2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Texture2], 
  min(CASE when [GEODETAILS].[NAME] = 'Lith1_Structure2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Structure2], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Texture2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Texture2], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Structure2' 
  then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Structure2] FROM [GEODETAILS] GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] 
  AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], 
  [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], 
min(CASE when [GEOBIGCOMMENT].[NAME] = 'Lith_Comments' 
then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Lith_Comments] FROM [GEOBIGCOMMENT] WHERE [GEOBIGCOMMENT].[NAME] IN ('Lith_Comments') 
GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] 
ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO]
 AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Lith_Colour] IS NOT NULL OR [Lith_Comments] IS NOT NULL 
 OR [Lith_RockName] IS NOT NULL OR [Lith1_pct] IS NOT NULL OR [Lith1_Structure] IS NOT NULL OR [Lith1_Texture] IS NOT NULL OR [Lith2_Colour] IS NOT NULL OR [Lith2_pct] IS NOT NULL
  OR [Lith2_RockName] IS NOT NULL OR [Lith2_Structure] IS NOT NULL OR [Lith2_Texture] IS NOT NULL OR [Lith_CompRock] IS NOT NULL OR [Lith_Colour_Int] IS NOT NULL OR [Lith_Colour2_Int] IS NOT NULL 
  OR [Lith_Colour2] IS NOT NULL OR [Lith1_Texture2] IS NOT NULL OR [Lith1_Structure2] IS NOT NULL OR [Lith2_Texture2] IS NOT NULL OR [Lith2_Structure2] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) AS [TMPVIEW915]) [TMPSQLSHEETVIEW]) a


outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, b.Secondary_Lithology_rus_Desc_D as 'Lith3_RockName',
                                   row_number() over (order by geolfrom) as rn_sec 
                              From 
							  (SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Lith_BeddingForm_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_BeddingForm_Desc_D] + ', '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_RockName_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_RockName_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Colour_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith3_Colour_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Structure_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith3_Structure_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Alpha]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Str2_Top_Alpha])
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Str2_Bot_Alpha])
END )  AS [Secondary_Lithology_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Lith_BeddingForm_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_BeddingForm_rus_Desc_D] + ', '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_RockName_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_RockName] + ' - ' + [ACQDERIVEDVIEW].[Lith3_RockName_rus_Desc_D] + ', '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Colour_Int_rus_Desc_D] is null THEN '' ELSE  [ACQDERIVEDVIEW].[Lith3_Colour_Int_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Colour_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_Colour_rus_Desc_D] + ' цвет'
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D] is null THEN '' ELSE ', структура ' + [ACQDERIVEDVIEW].[Lith3_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Structure_rus_Desc_D] is null THEN '' ELSE ', текстура ' + [ACQDERIVEDVIEW].[Lith3_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Top_Type] is null THEN '' ELSE '. Верхний контакт: ' + [ACQDERIVEDVIEW].[Str2_Top_Type_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Top_Appear] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str2_Top_Appear_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Alpha]) is null THEN '' ELSE ', ' + 'уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Top_Alpha]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Beta]) is null THEN '' ELSE ', ' + 'уг.beta' + ltrim(str([ACQDERIVEDVIEW].[Str2_Top_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Bot_Type] is null THEN '' ELSE '. Нижний контакт: ' + [ACQDERIVEDVIEW].[Str2_Bot_Type_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Bot_Appear] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str2_Bot_Appear_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]) is null THEN '' ELSE ', ' + 'уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]))
END+
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Beta]) is null THEN '' ELSE ', ' + 'уг beta ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Bot_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Comments] is null THEN '' ELSE ', (' + [ACQDERIVEDVIEW].[Lith3_Comments] + ')'
END
 )  AS [Secondary_Lithology_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_BeddingForm'
and LOOKUP = [DFINDF].[Lith_BeddingForm]
and ACTIVE = 1 )  AS [Lith_BeddingForm_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Colour'
and LOOKUP = [DFINDF].[Lith3_Colour]
and ACTIVE = 1 )  AS [Lith3_Colour_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_RockName'
and LOOKUP = [DFINDF].[Lith3_RockName]
and ACTIVE = 1 )  AS [Lith3_RockName_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Structure'
and LOOKUP = [DFINDF].[Lith3_Structure]
and ACTIVE = 1 )  AS [Lith3_Structure_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Texture'
and LOOKUP = [DFINDF].[Lith3_Texture]
and ACTIVE = 1 )  AS [Lith3_Texture_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_BeddingForm'
and LOOKUP = [DFINDF].[Lith_BeddingForm]
and ACTIVE = 1 )  AS [Lith_BeddingForm_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_RockName'
and LOOKUP = [DFINDF].[Lith3_RockName]
and ACTIVE = 1 )  AS [Lith3_RockName_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Colour_Int'
and LOOKUP = [DFINDF].[Lith3_Colour_Int]
and ACTIVE = 1

 )  AS [Lith3_Colour_Int_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Colour'
and LOOKUP = [DFINDF].[Lith3_Colour]
and ACTIVE = 1 )  AS [Lith3_Colour_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Texture'
and LOOKUP = [DFINDF].[Lith3_Texture]
and ACTIVE = 1 )  AS [Lith3_Texture_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Structure'
and LOOKUP = [DFINDF].[Lith3_Structure]
and ACTIVE = 1 )  AS [Lith3_Structure_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Top_Type'
and LOOKUP = [DFINDF].[Str2_Top_Type]
and ACTIVE = 1 )  AS [Str2_Top_Type_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Top_Appear'
and LOOKUP = [DFINDF].[Str2_Top_Appear]
and ACTIVE = 1 )  AS [Str2_Top_Appear_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Bot_Type'
and LOOKUP = [DFINDF].[Str2_Bot_Type]
and ACTIVE = 1 )  AS [Str2_Bot_Type_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Bot_Appear'
and LOOKUP = [DFINDF].[Str2_Bot_Appear]
and ACTIVE = 1 )  AS [Str2_Bot_Appear_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Str2_Bot_Alpha], [Str2_Top_Alpha], [Str2_Top_Type], [Str2_Bot_Type], [Str2_Top_Beta], [Lith3_Comments], [Str2_Top_Appear], [Lith3_RockName], [Str2_Bot_Beta], [Str2_Bot_Appear] 
FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], [GEODETAILS].[Lith_BeddingForm], [GEODETAILS].[Lith3_Colour], [GEODETAILS].[Lith3_RockName], [GEODETAILS].[Lith3_Structure], [GEODETAILS].[Lith3_Texture], 
CAST([GEODETAILS].[Str2_Bot_Alpha] AS FLOAT) AS [Str2_Bot_Alpha], CAST([GEODETAILS].[Str2_Top_Alpha] AS FLOAT) AS [Str2_Top_Alpha], [GEODETAILS].[Lith3_Colour_Int], [GEODETAILS].[Str2_Top_Type], [GEODETAILS].[Str2_Top_Appear], CAST([GEODETAILS].[Str2_Top_Beta] AS FLOAT) AS [Str2_Top_Beta], 
[GEODETAILS].[Str2_Bot_Type], [GEODETAILS].[Str2_Bot_Appear], CAST([GEODETAILS].[Str2_Bot_Beta] AS FLOAT) AS [Str2_Bot_Beta], [GEOBIGCOMMENT].[Lith3_Comments] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] 
AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Lith_BeddingForm' 
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_BeddingForm], min(CASE when [GEODETAILS].[NAME] = 'Lith3_Colour' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_Colour], min(CASE when [GEODETAILS].[NAME] = 'Lith3_RockName' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_RockName], 
min(CASE when [GEODETAILS].[NAME] = 'Lith3_Structure' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_Structure], min(CASE when [GEODETAILS].[NAME] = 'Lith3_Texture' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_Texture], min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Alpha' then 
CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Bot_Alpha], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Alpha' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Top_Alpha], min(CASE when [GEODETAILS].[NAME] = 'Lith3_Colour_Int' then [GEODETAILS].[VALUE] ELSE NULL END) 
as [Lith3_Colour_Int], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Type' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Top_Type], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Appear' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Top_Appear], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Beta' 
then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Top_Beta], min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Type' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Bot_Type], min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Appear' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Bot_Appear],
 min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Beta' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Bot_Beta] FROM [GEODETAILS] GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] 
 ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN 
 (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], min(CASE when [GEOBIGCOMMENT].[NAME] = 'Lith3_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Lith3_Comments] FROM [GEOBIGCOMMENT] 
 WHERE [GEOBIGCOMMENT].[NAME] IN ('Lith3_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] AND 
 [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Lith_BeddingForm] IS NOT NULL OR [Lith3_Colour] IS NOT NULL
  OR [Lith3_RockName] IS NOT NULL OR [Lith3_Structure] IS NOT NULL OR [Lith3_Texture] IS NOT NULL OR [Str2_Bot_Alpha] IS NOT NULL OR [Str2_Top_Alpha] IS NOT NULL OR [Lith3_Colour_Int] IS NOT NULL OR [Str2_Top_Type] IS NOT NULL OR [Str2_Top_Appear] IS NOT NULL OR [Str2_Top_Beta] IS NOT NULL 
  OR [Str2_Bot_Type] IS NOT NULL OR [Str2_Bot_Appear] IS NOT NULL OR [Str2_Bot_Beta] IS NOT NULL OR [Lith3_Comments] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) AS [TMPVIEW263]) [TMPSQLSHEETVIEW]
) b 
                             Where a.holeid = b.holeid  
                               and a.geolto>b.geolfrom and a.geolfrom<b.geolto) Sec
					  full join 
					        (Select geolfrom as geolfrom_alt, geolto as geolto_alt, c.Alteration_Zone_rus_Desc_D as 'Alteration',
                                   row_number() over (order by geolfrom) as rn_alt
                              From 
							  (SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Alteration_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Alteration_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Intensity_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Intensity_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Occurence_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Min_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt1_Min_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt1_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Min_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt2_Min_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt2_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Min_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt3_Min_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt3_Morphology_Desc_D]
END )  AS [Alteration_Zone_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Alteration_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Alteration_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Occurence_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Intensity_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Intensity_rus_Desc_D] + ' интенсивность'
END +

CASE WHEN [ACQDERIVEDVIEW].[Alt1_Min_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt1_Min_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Morphology_rus_Desc_D] is null THEN '' ELSE ' - ' + [ACQDERIVEDVIEW].[Alt1_Morphology_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Min_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt2_Min_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Morphology_rus_Desc_D] is null THEN '' ELSE ' - ' + [ACQDERIVEDVIEW].[Alt2_Morphology_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Min_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt3_Min_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Morphology_rus_Desc_D] is null THEN '' ELSE ' - ' + [ACQDERIVEDVIEW].[Alt3_Morphology_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Weathering_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Weathering_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Comments] is null THEN '' ELSE ', (' + [ACQDERIVEDVIEW].[Alt_Comments] + ')'
END )  AS [Alteration_Zone_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Intensity'
and LOOKUP = [DFINDF].[Alt_Intensity]
and ACTIVE = 1 )  AS [Alt_Intensity_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Occurence'
and LOOKUP = [DFINDF].[Alt_Occurence]
and ACTIVE = 1 )  AS [Alt_Occurence_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Min'
and LOOKUP = [DFINDF].[Alt1_Min]
and ACTIVE = 1 )  AS [Alt1_Min_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Morphology'
and LOOKUP = [DFINDF].[Alt1_Morphology]
and ACTIVE = 1 )  AS [Alt1_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Min'
and LOOKUP = [DFINDF].[Alt2_Min]
and ACTIVE = 1 )  AS [Alt2_Min_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Morphology'
and LOOKUP = [DFINDF].[Alt2_Morphology]
and ACTIVE = 1 )  AS [Alt2_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Min'
and LOOKUP = [DFINDF].[Alt3_Min]
and ACTIVE = 1 )  AS [Alt3_Min_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Morphology'
and LOOKUP = [DFINDF].[Alt3_Morphology]
and ACTIVE = 1 )  AS [Alt3_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alteration'
and LOOKUP = [DFINDF].[Alteration]
and ACTIVE = 1 )  AS [Alteration_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Intensity'
and LOOKUP = [DFINDF].[Alt_Intensity]
and ACTIVE = 1 )  AS [Alt_Intensity_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Occurence'
and LOOKUP = [DFINDF].[Alt_Occurence]
and ACTIVE = 1 )  AS [Alt_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Weathering'
and LOOKUP = [DFINDF].[Alt_Weathering]
and ACTIVE = 1 )  AS [Alt_Weathering_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Min'
and LOOKUP = [DFINDF].[Alt1_Min]
and ACTIVE = 1 )  AS [Alt1_Min_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Morphology'
and LOOKUP = [DFINDF].[Alt1_Morphology]
and ACTIVE = 1 )  AS [Alt1_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Min'
and LOOKUP = [DFINDF].[Alt2_Min]
and ACTIVE = 1 )  AS [Alt2_Min_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Morphology'
and LOOKUP = [DFINDF].[Alt2_Morphology]
and ACTIVE = 1 )  AS [Alt2_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Min'
and LOOKUP = [DFINDF].[Alt3_Min]
and ACTIVE = 1 )  AS [Alt3_Min_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Morphology'
and LOOKUP = [DFINDF].[Alt3_Morphology]
and ACTIVE = 1 )  AS [Alt3_Morphology_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alteration'
and LOOKUP = [DFINDF].[Alteration]
and ACTIVE = 1 )  AS [Alteration_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Alt_Comments] FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], [GEODETAILS].[Alt_Intensity], 
[GEODETAILS].[Alt_Occurence], [GEODETAILS].[Alt1_Min], [GEODETAILS].[Alt1_Morphology], [GEODETAILS].[Alt2_Min], [GEODETAILS].[Alt2_Morphology], [GEODETAILS].[Alt3_Min], [GEODETAILS].[Alt3_Morphology], [GEODETAILS].[Alteration], [GEOBIGCOMMENT].[Alt_Comments], [GEODETAILS].[Alt_Weathering] 
FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE],
 [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Alt_Intensity' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt_Intensity], min(CASE when [GEODETAILS].[NAME] = 'Alt_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt_Occurence],
  min(CASE when [GEODETAILS].[NAME] = 'Alt1_Min' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt1_Min], min(CASE when [GEODETAILS].[NAME] = 'Alt1_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt1_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Alt2_Min' then [GEODETAILS].[VALUE] ELSE NULL END) 
  as [Alt2_Min], min(CASE when [GEODETAILS].[NAME] = 'Alt2_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt2_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Alt3_Min' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt3_Min], min(CASE when [GEODETAILS].[NAME] = 'Alt3_Morphology' 
  then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt3_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Alteration' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alteration], min(CASE when [GEODETAILS].[NAME] = 'Alt_Weathering' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt_Weathering] FROM [GEODETAILS] 
  WHERE [GEODETAILS].[NAME] IN ('Alt_Intensity', 'Alt_Occurence', 'Alt1_Min', 'Alt1_Morphology', 'Alt2_Min', 'Alt2_Morphology', 'Alt3_Min', 'Alt3_Morphology', 'Alteration', 'Alt_Weathering') GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], 
  [GEODETAILS].[HOLEID])[GEODETAILS] ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] 
  LEFT JOIN (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], min(CASE when [GEOBIGCOMMENT].[NAME] = 'Alt_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Alt_Comments] FROM [GEOBIGCOMMENT] 
  WHERE [GEOBIGCOMMENT].[NAME] IN ('Alt_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE]
   AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Alt_Intensity] IS NOT NULL OR [Alt_Occurence] IS NOT NULL 
   OR [Alt1_Min] IS NOT NULL OR [Alt1_Morphology] IS NOT NULL OR [Alt2_Min] IS NOT NULL OR [Alt2_Morphology] IS NOT NULL OR [Alt3_Min] IS NOT NULL OR [Alt3_Morphology] IS NOT NULL OR [Alteration] IS NOT NULL OR [Alt_Comments] IS NOT NULL OR [Alt_Weathering] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) 
   AS [TMPVIEW271]) [TMPSQLSHEETVIEW] ) c 
                             Where a.holeid = c.holeid  
                               and a.geolto>c.geolfrom and a.geolfrom<c.geolto and a.GEOLFROM<=c.GEOLFROM) alt
						 on rn_sec = rn_alt
					  full join 
					 
					        (Select geolfrom as geolfrom_str, geolto as geolto_str, s.Structural_Data_rus_Desc_D as 'Strucrure',
                                   row_number() over (order by geolfrom) as rn_struc
                              From 
							  (SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Str_Elements_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Str_Elements_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Intensity_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Intensity_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr]) is null THEN '' ELSE ', from ' + str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo]) is null THEN '' ELSE ' to ' + str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo])
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_Beta]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Str_Beta])
END  )  AS [Structural_Data_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Str_Elements_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Str_Elements_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Intensity_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Intensity_rus_Desc_D]
END +

CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr]) is null THEN '' ELSE
(CASE WHEN [ACQDERIVEDVIEW].[Str_Elements] = 'CONT'  THEN ' на отм.' + str([ACQDERIVEDVIEW].[GEOLTO],7,2) + 'м под уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])) ELSE 
' уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])) END )

END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Type] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Type_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Appearance] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Appearance_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo]) is null THEN '' ELSE '-' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_Beta]) is null THEN '' ELSE ', ' + 'уг beta ' + ltrim(str([ACQDERIVEDVIEW].[Str_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Struc_Comments] is null THEN '' ELSE ', ( ' + [ACQDERIVEDVIEW].[Struc_Comments] + ')'
END )  AS [Structural_Data_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Elements'
and LOOKUP = [DFINDF].[Str_Elements]
and ACTIVE = 1 )  AS [Str_Elements_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Intensity'
and LOOKUP = [DFINDF].[Str_Intensity]
and ACTIVE = 1 )  AS [Str_Intensity_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Appearance'
and LOOKUP = [DFINDF].[Str_Appearance]
and ACTIVE = 1 )  AS [Str_Appearance_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Elements'
and LOOKUP = [DFINDF].[Str_Elements]
and ACTIVE = 1 )  AS [Str_Elements_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Intensity'
and LOOKUP = [DFINDF].[Str_Intensity]
and ACTIVE = 1 )  AS [Str_Intensity_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Type'
and LOOKUP = [DFINDF].[Str_Type]
and ACTIVE = 1 )  AS [Str_Type_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Str_AlphaAngleCoreFr], [Str_AlphaAngleCoreTo], [Str_Beta], [Str_Appearance], [Struc_Comments], [Str_Type], [Str_Elements] 
FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], CAST([GEODETAILS].[Str_AlphaAngleCoreFr] AS FLOAT) AS [Str_AlphaAngleCoreFr], CAST([GEODETAILS].[Str_AlphaAngleCoreTo] AS FLOAT) 
AS [Str_AlphaAngleCoreTo], CAST([GEODETAILS].[Str_Beta] AS FLOAT) AS [Str_Beta], [GEODETAILS].[Str_Elements], [GEODETAILS].[Str_Intensity], [GEODETAILS].[Str_Appearance], [GEODETAILS].[Str_Type], [GEOBIGCOMMENT].[Struc_Comments] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) 
AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], 
min(CASE when [GEODETAILS].[NAME] = 'Str_AlphaAngleCoreFr' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str_AlphaAngleCoreFr], min(CASE when [GEODETAILS].[NAME] = 'Str_AlphaAngleCoreTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str_AlphaAngleCoreTo], 
min(CASE when [GEODETAILS].[NAME] = 'Str_Beta' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str_Beta], min(CASE when [GEODETAILS].[NAME] = 'Str_Elements' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Elements], min(CASE when [GEODETAILS].[NAME] = 'Str_Intensity' 
then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Intensity], min(CASE when [GEODETAILS].[NAME] = 'Str_Appearance' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Appearance], min(CASE when [GEODETAILS].[NAME] = 'Str_Type' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Type] FROM [GEODETAILS]
 WHERE [GEODETAILS].[NAME] IN ('Str_AlphaAngleCoreFr', 'Str_AlphaAngleCoreTo', 'Str_Beta', 'Str_Elements', 'Str_Intensity', 'Str_Appearance', 'Str_Type') GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] 
  ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN 
 (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], min(CASE when [GEOBIGCOMMENT].[NAME] = 'Struc_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Struc_Comments] FROM [GEOBIGCOMMENT] 
 WHERE [GEOBIGCOMMENT].[NAME] IN ('Struc_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] 
 AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Str_AlphaAngleCoreFr] IS NOT NULL OR [Str_AlphaAngleCoreTo] IS NOT NULL
  OR [Str_Beta] IS NOT NULL OR [Str_Elements] IS NOT NULL OR [Str_Intensity] IS NOT NULL OR [Str_Appearance] IS NOT NULL OR [Str_Type] IS NOT NULL OR [Struc_Comments] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) AS [TMPVIEW267]) [TMPSQLSHEETVIEW] ) s 
                             Where a.holeid = s.holeid  
                               and a.geolto>s.geolfrom and a.geolfrom<s.geolto and a.GEOLFROM<=s.GEOLFROM ) struc
						 on rn_sec = rn_struc
						 full join
					        (Select geolfrom as geolfrom_min, geolto as geolto_min,m.Mineralization_Zone_rus_Desc_D  as 'Mineralisation',
                                   row_number() over (order by geolfrom) as rn_min
                              From (
							  SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Mineralization_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Mineralization_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Occurence_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_IntensityPct]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Min_IntensityPct]) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization2_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization2_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Occurence_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_IntensityPct]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Min2_IntensityPct]) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization3_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization3_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Occurence_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_IntensityPct]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Min3_IntensityPct]) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization4_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization4_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization5_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization5_Desc_D]
END )  AS [Mineralization_Zone_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Mineralization_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Mineralization_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min_SizeTo])) + 'мм'
END +


CASE WHEN [ACQDERIVEDVIEW].[Min_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min_IntensityPct],4,1)) + '%'
END +




CASE WHEN [ACQDERIVEDVIEW].[Mineralization2_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min2_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min2_SizeTo])) + 'мм'
END +



CASE WHEN [ACQDERIVEDVIEW].[Min2_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min2_IntensityPct],4,1)) + '%'
END +


CASE WHEN [ACQDERIVEDVIEW].[Mineralization3_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization3_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min3_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min3_SizeTo])) + 'мм'
END +


CASE WHEN [ACQDERIVEDVIEW].[Min3_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min3_IntensityPct],4,1)) + '%'
END +




CASE WHEN [ACQDERIVEDVIEW].[Mineralization4_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization4_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min4_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min4_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min4_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min4_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min4_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min4_SizeTo])) + 'мм'
END +

CASE WHEN [ACQDERIVEDVIEW].[Min4_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min4_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min4_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min4_IntensityPct],4,1)) + '%'
END +






CASE WHEN [ACQDERIVEDVIEW].[Mineralization5_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization5_rus_Desc_D]
END +

CASE WHEN [ACQDERIVEDVIEW].[Min5_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min5_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min5_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min5_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min5_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min5_SizeTo])) + 'мм'
END +

CASE WHEN [ACQDERIVEDVIEW].[Min5_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min5_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min5_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min5_IntensityPct],4,1)) + '%'
END +





CASE WHEN [ACQDERIVEDVIEW].[Min_Comments] is null THEN '' ELSE ', (' + [ACQDERIVEDVIEW].[Min_Comments] + ')'
END )  AS [Mineralization_Zone_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Morphology'
and LOOKUP = [DFINDF].[Min_Morphology]
and ACTIVE = 1 )  AS [Min_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Occurence'
and LOOKUP = [DFINDF].[Min_Occurence]
and ACTIVE = 1 )  AS [Min_Occurence_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Morphology'
and LOOKUP = [DFINDF].[Min2_Morphology]
and ACTIVE = 1 )  AS [Min2_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Occurence'
and LOOKUP = [DFINDF].[Min2_Occurence]
and ACTIVE = 1 )  AS [Min2_Occurence_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Morphology'
and LOOKUP = [DFINDF].[Min3_Morphology]
and ACTIVE = 1 )  AS [Min3_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Occurence'
and LOOKUP = [DFINDF].[Min3_Occurence]
and ACTIVE = 1 )  AS [Min3_Occurence_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation'
and LOOKUP = [DFINDF].[Mineralisation]
and ACTIVE = 1 )  AS [Mineralization_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation2'
and LOOKUP = [DFINDF].[Mineralisation2]
and ACTIVE = 1 )  AS [Mineralization2_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation3'
and LOOKUP = [DFINDF].[Mineralisation3]
and ACTIVE = 1 )  AS [Mineralization3_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation4'
and LOOKUP = [DFINDF].[Mineralisation4]
and ACTIVE = 1 )  AS [Mineralization4_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation5'
and LOOKUP = [DFINDF].[Mineralisation5]
and ACTIVE = 1 )  AS [Mineralization5_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Morphology'
and LOOKUP = [DFINDF].[Min_Morphology]
and ACTIVE = 1 )  AS [Min_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Occurence'
and LOOKUP = [DFINDF].[Min_Occurence]
and ACTIVE = 1 )  AS [Min_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Morphology'
and LOOKUP = [DFINDF].[Min2_Morphology]
and ACTIVE = 1 )  AS [Min2_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Occurence'
and LOOKUP = [DFINDF].[Min2_Occurence]
and ACTIVE = 1 )  AS [Min2_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Morphology'
and LOOKUP = [DFINDF].[Min3_Morphology]
and ACTIVE = 1 )  AS [Min3_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Occurence'
and LOOKUP = [DFINDF].[Min3_Occurence]
and ACTIVE = 1 )  AS [Min3_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min4_Morphology'
and LOOKUP = [DFINDF].[Min4_Morphology]
and ACTIVE = 1 )  AS [Min4_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min4_Occurence'
and LOOKUP = [DFINDF].[Min4_Occurence]
and ACTIVE = 1 )  AS [Min4_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min5_Morphology'
and LOOKUP = [DFINDF].[Min5_Morphology]
and ACTIVE = 1 )  AS [Min5_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min5_Occurence'
and LOOKUP = [DFINDF].[Min5_Occurence]
and ACTIVE = 1 )  AS [Min5_Occurence_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation'
and LOOKUP = [DFINDF].[Mineralisation]
and ACTIVE = 1 )  AS [Mineralization_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation2'
and LOOKUP = [DFINDF].[Mineralisation2]
and ACTIVE = 1 )  AS [Mineralization2_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation3'
and LOOKUP = [DFINDF].[Mineralisation3]
and ACTIVE = 1 )  AS [Mineralization3_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation4'
and LOOKUP = [DFINDF].[Mineralisation4]
and ACTIVE = 1 )  AS [Mineralization4_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation5'
and LOOKUP = [DFINDF].[Mineralisation5]
and ACTIVE = 1 )  AS [Mineralization5_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Min_IntensityPct], [Min2_IntensityPct], [Min3_IntensityPct], [Min4_SizeFrom], [Min2_SizeTo], [Min5_SizeTo], [Min_Comments], [Min5_SizeFrom], [Min_SizeTo], [Min2_SizeFrom], [Min3_SizeTo],
 [Min4_IntensityPct], [Min5_IntensityPct], [Min_SizeFrom], [Min3_SizeFrom], [Min4_SizeTo] FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], CAST([GEODETAILS].[Min_IntensityPct] AS FLOAT) AS [Min_IntensityPct], 
 [GEODETAILS].[Min_Morphology], [GEODETAILS].[Min_Occurence], CAST([GEODETAILS].[Min2_IntensityPct] AS FLOAT) AS [Min2_IntensityPct], [GEODETAILS].[Min2_Morphology], [GEODETAILS].[Min2_Occurence], CAST([GEODETAILS].[Min3_IntensityPct] AS FLOAT) AS [Min3_IntensityPct], [GEODETAILS].[Min3_Morphology], 
 [GEODETAILS].[Min3_Occurence], [GEODETAILS].[Mineralisation], [GEODETAILS].[Mineralisation2], [GEODETAILS].[Mineralisation3], [GEODETAILS].[Mineralisation4], [GEODETAILS].[Mineralisation5], [GEOBIGCOMMENT].[Min_Comments], CAST([GEODETAILS].[Min_SizeFrom] AS FLOAT) AS [Min_SizeFrom], CAST([GEODETAILS].[Min_SizeTo] 
 AS FLOAT) AS [Min_SizeTo], CAST([GEODETAILS].[Min2_SizeFrom] AS FLOAT) AS [Min2_SizeFrom], CAST([GEODETAILS].[Min2_SizeTo] AS FLOAT) AS [Min2_SizeTo], CAST([GEODETAILS].[Min3_SizeFrom] AS FLOAT) AS [Min3_SizeFrom], CAST([GEODETAILS].[Min3_SizeTo] AS FLOAT) AS [Min3_SizeTo], CAST([GEODETAILS].[Min4_IntensityPct] 
 AS FLOAT) AS [Min4_IntensityPct], [GEODETAILS].[Min4_Morphology], [GEODETAILS].[Min4_Occurence], CAST([GEODETAILS].[Min4_SizeFrom] AS FLOAT) AS [Min4_SizeFrom], CAST([GEODETAILS].[Min4_SizeTo] AS FLOAT) AS [Min4_SizeTo], CAST([GEODETAILS].[Min5_IntensityPct] AS FLOAT) AS [Min5_IntensityPct],
  [GEODETAILS].[Min5_Morphology], [GEODETAILS].[Min5_Occurence], CAST([GEODETAILS].[Min5_SizeFrom] AS FLOAT) AS [Min5_SizeFrom], CAST([GEODETAILS].[Min5_SizeTo] AS FLOAT) AS [Min5_SizeTo] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] 
  AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Min_IntensityPct' then
   CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min_Occurence], 
   min(CASE when [GEODETAILS].[NAME] = 'Min2_IntensityPct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min2_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min2_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min2_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min2_Occurence' 
   then [GEODETAILS].[VALUE] ELSE NULL END) as [Min2_Occurence], min(CASE when [GEODETAILS].[NAME] = 'Min3_IntensityPct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min3_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min3_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min3_Morphology], 
   min(CASE when [GEODETAILS].[NAME] = 'Min3_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min3_Occurence], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation2' then [GEODETAILS].[VALUE] ELSE
    NULL END) as [Mineralisation2], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation3' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation3], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation4' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation4], min(CASE when 
	[GEODETAILS].[NAME] = 'Mineralisation5' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation5], min(CASE when [GEODETAILS].[NAME] = 'Min_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min_SizeTo' then CAST([GEODETAILS].[VALUE] 
	AS FLOAT) ELSE NULL END) as [Min_SizeTo], min(CASE when [GEODETAILS].[NAME] = 'Min2_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min2_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min2_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min2_SizeTo], 
	min(CASE when [GEODETAILS].[NAME] = 'Min3_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min3_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min3_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min3_SizeTo], min(CASE when [GEODETAILS].[NAME] = 'Min4_IntensityPct' 
	then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min4_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min4_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min4_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min4_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min4_Occurence], 
	min(CASE when [GEODETAILS].[NAME] = 'Min4_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min4_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min4_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min4_SizeTo], min(CASE when [GEODETAILS].[NAME] = 'Min5_IntensityPct' 
	then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min5_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min5_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min5_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min5_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min5_Occurence],
	min(CASE when [GEODETAILS].[NAME] = 'Min5_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min5_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min5_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min5_SizeTo] FROM [GEODETAILS] GROUP BY [GEODETAILS].[PROJECTCODE], 
	[GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] 
		AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], 
		min(CASE when [GEOBIGCOMMENT].[NAME] = 'Min_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Min_Comments] FROM [GEOBIGCOMMENT] WHERE [GEOBIGCOMMENT].[NAME] IN ('Min_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], 
		[GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] 
		AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Min_IntensityPct] IS NOT NULL OR [Min_Morphology] IS NOT NULL OR [Min_Occurence] IS NOT NULL OR [Min2_IntensityPct] IS NOT NULL OR [Min2_Morphology] IS NOT NULL OR [Min2_Occurence] IS NOT NULL OR [Min3_IntensityPct] IS NOT NULL 
		OR [Min3_Morphology] IS NOT NULL OR [Min3_Occurence] IS NOT NULL OR [Mineralisation] IS NOT NULL OR [Mineralisation2] IS NOT NULL OR [Mineralisation3] IS NOT NULL OR [Mineralisation4] IS NOT NULL OR [Mineralisation5] IS NOT NULL OR [Min_Comments] IS NOT NULL OR [Min_SizeFrom] IS NOT NULL 
		OR [Min_SizeTo] IS NOT NULL OR [Min2_SizeFrom] IS NOT NULL OR [Min2_SizeTo] IS NOT NULL OR [Min3_SizeFrom] IS NOT NULL OR [Min3_SizeTo] IS NOT NULL OR [Min4_IntensityPct] IS NOT NULL OR [Min4_Morphology] IS NOT NULL OR [Min4_Occurence] IS NOT NULL OR [Min4_SizeFrom] IS NOT NULL OR [Min4_SizeTo] IS NOT NULL 
		OR [Min5_IntensityPct] IS NOT NULL OR [Min5_Morphology] IS NOT NULL OR [Min5_Occurence] IS NOT NULL OR [Min5_SizeFrom] IS NOT NULL OR [Min5_SizeTo] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) AS [TMPVIEW275]) [TMPSQLSHEETVIEW]) m 
                             Where a.holeid = m.holeid  
                               and a.geolto>m.geolfrom and a.geolfrom<m.geolto and a.GEOLFROM<=m.GEOLFROM) min on isnull(rn_sec,rn_alt) = rn_min
) others
--left join (select case row_number() over(partition by holeid order by geolfrom) when 1 then holeid end as x_holeid, * from GEODETAILS where name='Lith_RockName' or name='Alteration' or name='Mineralization' or name='Structure'
--)ord on ord.holeid=a.HOLEID and ord.GEOLFROM=a.GEOLFROM
WHere a.HOLEID='DZDZ-18-001'
--order by ord.HOLEID,ord.GEOLFROM
Order by a.holeid desc,a.geolfrom,a.Primary_Lithology_rus_Desc_D, COALESCE(others.rn_struc,others.rn_sec,others.rn_alt,others.rn_min) asc


К сообщению приложен файл. Размер - 130Kb
16 июл 18, 09:14    [21573170]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
katish444,
Вот в такой отчет я собираю данные из той таблички, и к сожалению интервалы совсем не по порядку. Подскажите, пожалуйста, как быть?

К сообщению приложен файл. Размер - 29Kb
16 июл 18, 09:15    [21573171]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
katish444,
хотелось бы дополнить, что интервалы идут по порядку, то есть сортировка работает, но между собой интервалы не бьются
16 июл 18, 09:16    [21573173]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
aleks222
Member

Откуда:
Сообщений: 957
katish444
katish444,
но между собой интервалы не бьются


"Битва интервалов" what the fuck?

ЗЫ. Я только повторюсь: используйте нормализованные даные и будет вам щастье.
16 июл 18, 09:20    [21573187]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
запрос, кончно, впечатляет...
16 июл 18, 09:25    [21573199]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
aleks222,
я не могу на это влиять, работаю с тем, что дают. Это не в моей компетенции.
Я добилась того, что интервалы каждый на своем месте, но тогда происходит дублирование.
Ты не мог бы подсказать команду, которая запретила бы нумерации дублироваться, ну или хотябы выводить на экран не дублированные данные
+
-- Type your SQL script here.
    select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid, 
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,[a].Primary_Lithology_rus_Desc_D order by a.geolfrom) when 1 then [a].Primary_Lithology_rus_Desc_D end as 'Primary Lithology',



geolfrom_sec,geolto_sec,Lith3_RockName,rn_sec,geolfrom_str, geolto_str, Strucrure,rn_struc, geolfrom_alt,geolto_alt,Alteration,rn_alt,geolfrom_min,geolto_min,Mineralisation, rn_min from

( SELECT  TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Lith_RockName_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_RockName_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Lith1_pct]) is null THEN '' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith1_pct])) + '%' + ')'
END + 
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith_Colour_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Texture_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Structure_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Structure_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_RockName_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_RockName_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Lith2_pct]) is null THEN '' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith2_pct])) + '%' + ')'
END + 
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Colour_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Colour_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Texture_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Structure_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Comments] is null THEN '' ELSE ', ( ' + [ACQDERIVEDVIEW].[Lith_Comments] + ')'
END
 )  AS [Primary_Lithology_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Lith_CompRock_rus_Desc_D] is null THEN '' ELSE  [ACQDERIVEDVIEW].[Lith_CompRock_rus_Desc_D]+': '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_RockName_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_RockName] + ' - ' + [ACQDERIVEDVIEW].[Lith_RockName_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_pct] = 100 THEN ',' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith1_pct])) + '%' + '),'
END + 
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour_Int_rus_Desc_D] is null THEN '' ELSE  ' ' +  [ACQDERIVEDVIEW].[Lith_Colour_Int_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour_rus_Desc_D] is null THEN '' ELSE ' ' +  [ACQDERIVEDVIEW].[Lith_Colour_rus_Desc_D] + ' цвет, '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour2_Int_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_Colour2_Int_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Colour2_rus_Desc_D] is null THEN '' ELSE  [ACQDERIVEDVIEW].[Lith_Colour2_rus_Desc_D] + ' - второстепенный цвет '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture_rus_Desc_D] is null THEN '' ELSE ', структура ' + [ACQDERIVEDVIEW].[Lith1_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Texture2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Structure_rus_Desc_D] is null THEN '' ELSE ', текстура ' + [ACQDERIVEDVIEW].[Lith1_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Structure2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith1_Structure2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_RockName_rus_Desc_D] is null THEN '' ELSE '; ' + [ACQDERIVEDVIEW].[Lith2_RockName] + ' - ' + [ACQDERIVEDVIEW].[Lith2_RockName_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Lith2_pct]) is null THEN '' ELSE '(' + ltrim(str([ACQDERIVEDVIEW].[Lith2_pct])) + '%' + ')'
END + 
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Colour_rus_Desc_D] is null THEN '' ELSE ', цвет ' + [ACQDERIVEDVIEW].[Lith2_Colour_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture_rus_Desc_D] is null THEN '' ELSE ', структура ' + [ACQDERIVEDVIEW].[Lith2_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Texture2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure_rus_Desc_D] is null THEN '' ELSE ', текстура ' + [ACQDERIVEDVIEW].[Lith2_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith_Comments] is null THEN '' ELSE ', (Прим. ' + [ACQDERIVEDVIEW].[Lith_Comments] + ')'
END  )  AS [Primary_Lithology_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour'
and LOOKUP = [DFINDF].[Lith_Colour]
and ACTIVE = 1

 )  AS [Lith_Colour_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_RockName'
and LOOKUP = [DFINDF].[Lith_RockName]
and ACTIVE = 1 )  AS [Lith_RockName_Desc_D],  ( select LOWER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Structure'
and LOOKUP = [DFINDF].[Lith1_Structure]
and ACTIVE = 1 )  AS [Lith1_Structure_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Texture'
and LOOKUP = [DFINDF].[Lith1_Texture]
and ACTIVE = 1 )  AS [Lith1_Texture_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Colour'
and LOOKUP = [DFINDF].[Lith2_Colour]
and ACTIVE = 1 )  AS [Lith2_Colour_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_RockName'
and LOOKUP = [DFINDF].[Lith2_RockName]
and ACTIVE = 1 )  AS [Lith2_RockName_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Structure'
and LOOKUP = [DFINDF].[Lith2_Structure]
and ACTIVE = 1 )  AS [Lith2_Structure_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Texture'
and LOOKUP = [DFINDF].[Lith2_Texture]
and ACTIVE = 1 )  AS [Lith2_Texture_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+UPPER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_CompRock'
and LOOKUP = [DFINDF].[Lith_CompRock]
and ACTIVE = 1 )  AS [Lith_CompRock_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_RockName'
and LOOKUP = [DFINDF].[Lith_RockName]
and ACTIVE = 1
 )  AS [Lith_RockName_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour_Int'
and LOOKUP = [DFINDF].[Lith_Colour_Int]
and ACTIVE = 1

 )  AS [Lith_Colour_Int_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour'
and LOOKUP = [DFINDF].[Lith_Colour]
and ACTIVE = 1

 )  AS [Lith_Colour_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour2_Int'
and LOOKUP = [DFINDF].[Lith_Colour2_Int]
and ACTIVE = 1

 )  AS [Lith_Colour2_Int_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_Colour2'
and LOOKUP = [DFINDF].[Lith_Colour2]
and ACTIVE = 1

 )  AS [Lith_Colour2_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Texture'
and LOOKUP = [DFINDF].[Lith1_Texture]
and ACTIVE = 1 )  AS [Lith1_Texture_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Texture2'
and LOOKUP = [DFINDF].[Lith1_Texture2]
and ACTIVE = 1 )  AS [Lith1_Texture2_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Structure'
and LOOKUP = [DFINDF].[Lith1_Structure]
and ACTIVE = 1 )  AS [Lith1_Structure_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith1_Structure2'
and LOOKUP = [DFINDF].[Lith1_Structure2]
and ACTIVE = 1 )  AS [Lith1_Structure2_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_RockName'
and LOOKUP = [DFINDF].[Lith2_RockName]
and ACTIVE = 1 )  AS [Lith2_RockName_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Colour'
and LOOKUP = [DFINDF].[Lith2_Colour]
and ACTIVE = 1 )  AS [Lith2_Colour_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Texture'
and LOOKUP = [DFINDF].[Lith2_Texture]
and ACTIVE = 1 )  AS [Lith2_Texture_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Texture2'
and LOOKUP = [DFINDF].[Lith2_Texture2]
and ACTIVE = 1 )  AS [Lith2_Texture2_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Structure'
and LOOKUP = [DFINDF].[Lith2_Structure]
and ACTIVE = 1 )  AS [Lith2_Structure_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith2_Structure2'
and LOOKUP = [DFINDF].[Lith2_Structure2]
and ACTIVE = 1 )  AS [Lith2_Structure2_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Lith_Comments], [Lith1_pct], [Lith2_pct], [Lith_RockName], [Lith2_RockName] 
FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], [GEODETAILS].[Lith_Colour], 
[GEOBIGCOMMENT].[Lith_Comments], [GEODETAILS].[Lith_RockName], CAST([GEODETAILS].[Lith1_pct] AS FLOAT) AS [Lith1_pct], [GEODETAILS].[Lith1_Structure], [GEODETAILS].[Lith1_Texture],
 [GEODETAILS].[Lith2_Colour], CAST([GEODETAILS].[Lith2_pct] AS FLOAT) AS [Lith2_pct], [GEODETAILS].[Lith2_RockName], [GEODETAILS].[Lith2_Structure], [GEODETAILS].[Lith2_Texture], 
 [GEODETAILS].[Lith_CompRock], [GEODETAILS].[Lith_Colour_Int], [GEODETAILS].[Lith_Colour2_Int], [GEODETAILS].[Lith_Colour2], [GEODETAILS].[Lith1_Texture2], [GEODETAILS].[Lith1_Structure2],
  [GEODETAILS].[Lith2_Texture2], [GEODETAILS].[Lith2_Structure2] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID]
    AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM],
	 [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour], min(CASE when [GEODETAILS].[NAME] = 'Lith_RockName' 
	 then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_RockName], min(CASE when [GEODETAILS].[NAME] = 'Lith1_pct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Lith1_pct], min(CASE when [GEODETAILS].[NAME] = 'Lith1_Structure' 
	 then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Structure], min(CASE when [GEODETAILS].[NAME] = 'Lith1_Texture' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Texture], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Colour' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Colour], 
min(CASE when [GEODETAILS].[NAME] = 'Lith2_pct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Lith2_pct], min(CASE when [GEODETAILS].[NAME] = 'Lith2_RockName' 
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_RockName], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Structure' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Structure], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Texture' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Texture],
 min(CASE when [GEODETAILS].[NAME] = 'Lith_CompRock' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_CompRock], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour_Int' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour_Int], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour2_Int'
  then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour2_Int], min(CASE when [GEODETAILS].[NAME] = 'Lith_Colour2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_Colour2], min(CASE when [GEODETAILS].[NAME] = 'Lith1_Texture2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Texture2], 
  min(CASE when [GEODETAILS].[NAME] = 'Lith1_Structure2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith1_Structure2], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Texture2' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Texture2], min(CASE when [GEODETAILS].[NAME] = 'Lith2_Structure2' 
  then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith2_Structure2] FROM [GEODETAILS] GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] 
  AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], 
  [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], 
min(CASE when [GEOBIGCOMMENT].[NAME] = 'Lith_Comments' 
then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Lith_Comments] FROM [GEOBIGCOMMENT] WHERE [GEOBIGCOMMENT].[NAME] IN ('Lith_Comments') 
GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] 
ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO]
 AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Lith_Colour] IS NOT NULL OR [Lith_Comments] IS NOT NULL 
 OR [Lith_RockName] IS NOT NULL OR [Lith1_pct] IS NOT NULL OR [Lith1_Structure] IS NOT NULL OR [Lith1_Texture] IS NOT NULL OR [Lith2_Colour] IS NOT NULL OR [Lith2_pct] IS NOT NULL
  OR [Lith2_RockName] IS NOT NULL OR [Lith2_Structure] IS NOT NULL OR [Lith2_Texture] IS NOT NULL OR [Lith_CompRock] IS NOT NULL OR [Lith_Colour_Int] IS NOT NULL OR [Lith_Colour2_Int] IS NOT NULL 
  OR [Lith_Colour2] IS NOT NULL OR [Lith1_Texture2] IS NOT NULL OR [Lith1_Structure2] IS NOT NULL OR [Lith2_Texture2] IS NOT NULL OR [Lith2_Structure2] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) AS [TMPVIEW915]) [TMPSQLSHEETVIEW]) a


outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, b.Secondary_Lithology_rus_Desc_D as 'Lith3_RockName',
                                   row_number() over (order by geolfrom) as rn_sec 
                              From 
							  (SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Lith_BeddingForm_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_BeddingForm_Desc_D] + ', '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_RockName_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_RockName_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Colour_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith3_Colour_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Structure_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith3_Structure_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Alpha]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Str2_Top_Alpha])
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Str2_Bot_Alpha])
END )  AS [Secondary_Lithology_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Lith_BeddingForm_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith_BeddingForm_rus_Desc_D] + ', '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_RockName_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_RockName] + ' - ' + [ACQDERIVEDVIEW].[Lith3_RockName_rus_Desc_D] + ', '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Colour_Int_rus_Desc_D] is null THEN '' ELSE  [ACQDERIVEDVIEW].[Lith3_Colour_Int_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Colour_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Lith3_Colour_rus_Desc_D] + ' цвет'
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D] is null THEN '' ELSE ', структура ' + [ACQDERIVEDVIEW].[Lith3_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Structure_rus_Desc_D] is null THEN '' ELSE ', текстура ' + [ACQDERIVEDVIEW].[Lith3_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Top_Type] is null THEN '' ELSE '. Верхний контакт: ' + [ACQDERIVEDVIEW].[Str2_Top_Type_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Top_Appear] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str2_Top_Appear_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Alpha]) is null THEN '' ELSE ', ' + 'уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Top_Alpha]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Beta]) is null THEN '' ELSE ', ' + 'уг.beta' + ltrim(str([ACQDERIVEDVIEW].[Str2_Top_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Bot_Type] is null THEN '' ELSE '. Нижний контакт: ' + [ACQDERIVEDVIEW].[Str2_Bot_Type_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Bot_Appear] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str2_Bot_Appear_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]) is null THEN '' ELSE ', ' + 'уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]))
END+
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Beta]) is null THEN '' ELSE ', ' + 'уг beta ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Bot_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Comments] is null THEN '' ELSE ', (' + [ACQDERIVEDVIEW].[Lith3_Comments] + ')'
END
 )  AS [Secondary_Lithology_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_BeddingForm'
and LOOKUP = [DFINDF].[Lith_BeddingForm]
and ACTIVE = 1 )  AS [Lith_BeddingForm_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Colour'
and LOOKUP = [DFINDF].[Lith3_Colour]
and ACTIVE = 1 )  AS [Lith3_Colour_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_RockName'
and LOOKUP = [DFINDF].[Lith3_RockName]
and ACTIVE = 1 )  AS [Lith3_RockName_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Structure'
and LOOKUP = [DFINDF].[Lith3_Structure]
and ACTIVE = 1 )  AS [Lith3_Structure_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Texture'
and LOOKUP = [DFINDF].[Lith3_Texture]
and ACTIVE = 1 )  AS [Lith3_Texture_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith_BeddingForm'
and LOOKUP = [DFINDF].[Lith_BeddingForm]
and ACTIVE = 1 )  AS [Lith_BeddingForm_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_RockName'
and LOOKUP = [DFINDF].[Lith3_RockName]
and ACTIVE = 1 )  AS [Lith3_RockName_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1)) +LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Colour_Int'
and LOOKUP = [DFINDF].[Lith3_Colour_Int]
and ACTIVE = 1

 )  AS [Lith3_Colour_Int_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Colour'
and LOOKUP = [DFINDF].[Lith3_Colour]
and ACTIVE = 1 )  AS [Lith3_Colour_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Texture'
and LOOKUP = [DFINDF].[Lith3_Texture]
and ACTIVE = 1 )  AS [Lith3_Texture_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Lith3_Structure'
and LOOKUP = [DFINDF].[Lith3_Structure]
and ACTIVE = 1 )  AS [Lith3_Structure_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Top_Type'
and LOOKUP = [DFINDF].[Str2_Top_Type]
and ACTIVE = 1 )  AS [Str2_Top_Type_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Top_Appear'
and LOOKUP = [DFINDF].[Str2_Top_Appear]
and ACTIVE = 1 )  AS [Str2_Top_Appear_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Bot_Type'
and LOOKUP = [DFINDF].[Str2_Bot_Type]
and ACTIVE = 1 )  AS [Str2_Bot_Type_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str2_Bot_Appear'
and LOOKUP = [DFINDF].[Str2_Bot_Appear]
and ACTIVE = 1 )  AS [Str2_Bot_Appear_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Str2_Bot_Alpha], [Str2_Top_Alpha], [Str2_Top_Type], [Str2_Bot_Type], [Str2_Top_Beta], [Lith3_Comments], [Str2_Top_Appear], [Lith3_RockName], [Str2_Bot_Beta], [Str2_Bot_Appear] 
FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], [GEODETAILS].[Lith_BeddingForm], [GEODETAILS].[Lith3_Colour], [GEODETAILS].[Lith3_RockName], [GEODETAILS].[Lith3_Structure], [GEODETAILS].[Lith3_Texture], 
CAST([GEODETAILS].[Str2_Bot_Alpha] AS FLOAT) AS [Str2_Bot_Alpha], CAST([GEODETAILS].[Str2_Top_Alpha] AS FLOAT) AS [Str2_Top_Alpha], [GEODETAILS].[Lith3_Colour_Int], [GEODETAILS].[Str2_Top_Type], [GEODETAILS].[Str2_Top_Appear], CAST([GEODETAILS].[Str2_Top_Beta] AS FLOAT) AS [Str2_Top_Beta], 
[GEODETAILS].[Str2_Bot_Type], [GEODETAILS].[Str2_Bot_Appear], CAST([GEODETAILS].[Str2_Bot_Beta] AS FLOAT) AS [Str2_Bot_Beta], [GEOBIGCOMMENT].[Lith3_Comments] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] 
AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Lith_BeddingForm' 
then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith_BeddingForm], min(CASE when [GEODETAILS].[NAME] = 'Lith3_Colour' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_Colour], min(CASE when [GEODETAILS].[NAME] = 'Lith3_RockName' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_RockName], 
min(CASE when [GEODETAILS].[NAME] = 'Lith3_Structure' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_Structure], min(CASE when [GEODETAILS].[NAME] = 'Lith3_Texture' then [GEODETAILS].[VALUE] ELSE NULL END) as [Lith3_Texture], min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Alpha' then 
CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Bot_Alpha], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Alpha' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Top_Alpha], min(CASE when [GEODETAILS].[NAME] = 'Lith3_Colour_Int' then [GEODETAILS].[VALUE] ELSE NULL END) 
as [Lith3_Colour_Int], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Type' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Top_Type], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Appear' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Top_Appear], min(CASE when [GEODETAILS].[NAME] = 'Str2_Top_Beta' 
then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Top_Beta], min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Type' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Bot_Type], min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Appear' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str2_Bot_Appear],
 min(CASE when [GEODETAILS].[NAME] = 'Str2_Bot_Beta' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str2_Bot_Beta] FROM [GEODETAILS] GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] 
 ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN 
 (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], min(CASE when [GEOBIGCOMMENT].[NAME] = 'Lith3_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Lith3_Comments] FROM [GEOBIGCOMMENT] 
 WHERE [GEOBIGCOMMENT].[NAME] IN ('Lith3_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] AND 
 [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Lith_BeddingForm] IS NOT NULL OR [Lith3_Colour] IS NOT NULL
  OR [Lith3_RockName] IS NOT NULL OR [Lith3_Structure] IS NOT NULL OR [Lith3_Texture] IS NOT NULL OR [Str2_Bot_Alpha] IS NOT NULL OR [Str2_Top_Alpha] IS NOT NULL OR [Lith3_Colour_Int] IS NOT NULL OR [Str2_Top_Type] IS NOT NULL OR [Str2_Top_Appear] IS NOT NULL OR [Str2_Top_Beta] IS NOT NULL 
  OR [Str2_Bot_Type] IS NOT NULL OR [Str2_Bot_Appear] IS NOT NULL OR [Str2_Bot_Beta] IS NOT NULL OR [Lith3_Comments] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) AS [TMPVIEW263]) [TMPSQLSHEETVIEW]
) b 
                             Where a.holeid = b.holeid  
                               and a.geolto>b.geolfrom and a.geolfrom<b.geolto) Sec
					  full join 
					        (Select geolfrom as geolfrom_alt, geolto as geolto_alt, c.Alteration_Zone_rus_Desc_D as 'Alteration',
                                   row_number() over (order by geolfrom) as rn_alt
                              From 
							  (SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Alteration_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Alteration_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Intensity_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Intensity_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Occurence_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Min_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt1_Min_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt1_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Min_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt2_Min_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt2_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Min_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt3_Min_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt3_Morphology_Desc_D]
END )  AS [Alteration_Zone_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Alteration_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Alteration_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Occurence_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Intensity_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Intensity_rus_Desc_D] + ' интенсивность'
END +

CASE WHEN [ACQDERIVEDVIEW].[Alt1_Min_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt1_Min_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt1_Morphology_rus_Desc_D] is null THEN '' ELSE ' - ' + [ACQDERIVEDVIEW].[Alt1_Morphology_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Min_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt2_Min_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt2_Morphology_rus_Desc_D] is null THEN '' ELSE ' - ' + [ACQDERIVEDVIEW].[Alt2_Morphology_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Min_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt3_Min_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt3_Morphology_rus_Desc_D] is null THEN '' ELSE ' - ' + [ACQDERIVEDVIEW].[Alt3_Morphology_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Weathering_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Alt_Weathering_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Alt_Comments] is null THEN '' ELSE ', (' + [ACQDERIVEDVIEW].[Alt_Comments] + ')'
END )  AS [Alteration_Zone_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Intensity'
and LOOKUP = [DFINDF].[Alt_Intensity]
and ACTIVE = 1 )  AS [Alt_Intensity_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Occurence'
and LOOKUP = [DFINDF].[Alt_Occurence]
and ACTIVE = 1 )  AS [Alt_Occurence_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Min'
and LOOKUP = [DFINDF].[Alt1_Min]
and ACTIVE = 1 )  AS [Alt1_Min_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Morphology'
and LOOKUP = [DFINDF].[Alt1_Morphology]
and ACTIVE = 1 )  AS [Alt1_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Min'
and LOOKUP = [DFINDF].[Alt2_Min]
and ACTIVE = 1 )  AS [Alt2_Min_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Morphology'
and LOOKUP = [DFINDF].[Alt2_Morphology]
and ACTIVE = 1 )  AS [Alt2_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Min'
and LOOKUP = [DFINDF].[Alt3_Min]
and ACTIVE = 1 )  AS [Alt3_Min_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Morphology'
and LOOKUP = [DFINDF].[Alt3_Morphology]
and ACTIVE = 1 )  AS [Alt3_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alteration'
and LOOKUP = [DFINDF].[Alteration]
and ACTIVE = 1 )  AS [Alteration_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Intensity'
and LOOKUP = [DFINDF].[Alt_Intensity]
and ACTIVE = 1 )  AS [Alt_Intensity_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Occurence'
and LOOKUP = [DFINDF].[Alt_Occurence]
and ACTIVE = 1 )  AS [Alt_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt_Weathering'
and LOOKUP = [DFINDF].[Alt_Weathering]
and ACTIVE = 1 )  AS [Alt_Weathering_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Min'
and LOOKUP = [DFINDF].[Alt1_Min]
and ACTIVE = 1 )  AS [Alt1_Min_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt1_Morphology'
and LOOKUP = [DFINDF].[Alt1_Morphology]
and ACTIVE = 1 )  AS [Alt1_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Min'
and LOOKUP = [DFINDF].[Alt2_Min]
and ACTIVE = 1 )  AS [Alt2_Min_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt2_Morphology'
and LOOKUP = [DFINDF].[Alt2_Morphology]
and ACTIVE = 1 )  AS [Alt2_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Min'
and LOOKUP = [DFINDF].[Alt3_Min]
and ACTIVE = 1 )  AS [Alt3_Min_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alt3_Morphology'
and LOOKUP = [DFINDF].[Alt3_Morphology]
and ACTIVE = 1 )  AS [Alt3_Morphology_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Alteration'
and LOOKUP = [DFINDF].[Alteration]
and ACTIVE = 1 )  AS [Alteration_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Alt_Comments] FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], [GEODETAILS].[Alt_Intensity], 
[GEODETAILS].[Alt_Occurence], [GEODETAILS].[Alt1_Min], [GEODETAILS].[Alt1_Morphology], [GEODETAILS].[Alt2_Min], [GEODETAILS].[Alt2_Morphology], [GEODETAILS].[Alt3_Min], [GEODETAILS].[Alt3_Morphology], [GEODETAILS].[Alteration], [GEOBIGCOMMENT].[Alt_Comments], [GEODETAILS].[Alt_Weathering] 
FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE],
 [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Alt_Intensity' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt_Intensity], min(CASE when [GEODETAILS].[NAME] = 'Alt_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt_Occurence],
  min(CASE when [GEODETAILS].[NAME] = 'Alt1_Min' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt1_Min], min(CASE when [GEODETAILS].[NAME] = 'Alt1_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt1_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Alt2_Min' then [GEODETAILS].[VALUE] ELSE NULL END) 
  as [Alt2_Min], min(CASE when [GEODETAILS].[NAME] = 'Alt2_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt2_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Alt3_Min' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt3_Min], min(CASE when [GEODETAILS].[NAME] = 'Alt3_Morphology' 
  then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt3_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Alteration' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alteration], min(CASE when [GEODETAILS].[NAME] = 'Alt_Weathering' then [GEODETAILS].[VALUE] ELSE NULL END) as [Alt_Weathering] FROM [GEODETAILS] 
  WHERE [GEODETAILS].[NAME] IN ('Alt_Intensity', 'Alt_Occurence', 'Alt1_Min', 'Alt1_Morphology', 'Alt2_Min', 'Alt2_Morphology', 'Alt3_Min', 'Alt3_Morphology', 'Alteration', 'Alt_Weathering') GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], 
  [GEODETAILS].[HOLEID])[GEODETAILS] ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] 
  LEFT JOIN (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], min(CASE when [GEOBIGCOMMENT].[NAME] = 'Alt_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Alt_Comments] FROM [GEOBIGCOMMENT] 
  WHERE [GEOBIGCOMMENT].[NAME] IN ('Alt_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE]
   AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Alt_Intensity] IS NOT NULL OR [Alt_Occurence] IS NOT NULL 
   OR [Alt1_Min] IS NOT NULL OR [Alt1_Morphology] IS NOT NULL OR [Alt2_Min] IS NOT NULL OR [Alt2_Morphology] IS NOT NULL OR [Alt3_Min] IS NOT NULL OR [Alt3_Morphology] IS NOT NULL OR [Alteration] IS NOT NULL OR [Alt_Comments] IS NOT NULL OR [Alt_Weathering] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) 
   AS [TMPVIEW271]) [TMPSQLSHEETVIEW] ) c 
                             Where a.holeid = c.holeid  
                               and a.geolto>c.geolfrom and a.geolfrom<c.geolto and a.GEOLFROM<=c.GEOLFROM) alt
						 on rn_sec = rn_alt
					  full join 
					 
					        (Select geolfrom as geolfrom_str, geolto as geolto_str, s.Structural_Data_rus_Desc_D as 'Strucrure',
                                   row_number() over (order by geolfrom) as rn_struc
                              From 
							  (SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Str_Elements_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Str_Elements_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Intensity_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Intensity_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr]) is null THEN '' ELSE ', from ' + str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo]) is null THEN '' ELSE ' to ' + str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo])
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_Beta]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Str_Beta])
END  )  AS [Structural_Data_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Str_Elements_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Str_Elements_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Intensity_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Intensity_rus_Desc_D]
END +

CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr]) is null THEN '' ELSE
(CASE WHEN [ACQDERIVEDVIEW].[Str_Elements] = 'CONT'  THEN ' на отм.' + str([ACQDERIVEDVIEW].[GEOLTO],7,2) + 'м под уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])) ELSE 
' уг.ок ' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])) END )

END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Type] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Type_rus_Desc_D] 
END +
CASE WHEN [ACQDERIVEDVIEW].[Str_Appearance] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Str_Appearance_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo]) is null THEN '' ELSE '-' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreTo]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str_Beta]) is null THEN '' ELSE ', ' + 'уг beta ' + ltrim(str([ACQDERIVEDVIEW].[Str_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Struc_Comments] is null THEN '' ELSE ', ( ' + [ACQDERIVEDVIEW].[Struc_Comments] + ')'
END )  AS [Structural_Data_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Elements'
and LOOKUP = [DFINDF].[Str_Elements]
and ACTIVE = 1 )  AS [Str_Elements_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Intensity'
and LOOKUP = [DFINDF].[Str_Intensity]
and ACTIVE = 1 )  AS [Str_Intensity_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Appearance'
and LOOKUP = [DFINDF].[Str_Appearance]
and ACTIVE = 1 )  AS [Str_Appearance_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Elements'
and LOOKUP = [DFINDF].[Str_Elements]
and ACTIVE = 1 )  AS [Str_Elements_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Intensity'
and LOOKUP = [DFINDF].[Str_Intensity]
and ACTIVE = 1 )  AS [Str_Intensity_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Str_Type'
and LOOKUP = [DFINDF].[Str_Type]
and ACTIVE = 1 )  AS [Str_Type_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Str_AlphaAngleCoreFr], [Str_AlphaAngleCoreTo], [Str_Beta], [Str_Appearance], [Struc_Comments], [Str_Type], [Str_Elements] 
FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], CAST([GEODETAILS].[Str_AlphaAngleCoreFr] AS FLOAT) AS [Str_AlphaAngleCoreFr], CAST([GEODETAILS].[Str_AlphaAngleCoreTo] AS FLOAT) 
AS [Str_AlphaAngleCoreTo], CAST([GEODETAILS].[Str_Beta] AS FLOAT) AS [Str_Beta], [GEODETAILS].[Str_Elements], [GEODETAILS].[Str_Intensity], [GEODETAILS].[Str_Appearance], [GEODETAILS].[Str_Type], [GEOBIGCOMMENT].[Struc_Comments] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) 
AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], 
min(CASE when [GEODETAILS].[NAME] = 'Str_AlphaAngleCoreFr' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str_AlphaAngleCoreFr], min(CASE when [GEODETAILS].[NAME] = 'Str_AlphaAngleCoreTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str_AlphaAngleCoreTo], 
min(CASE when [GEODETAILS].[NAME] = 'Str_Beta' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Str_Beta], min(CASE when [GEODETAILS].[NAME] = 'Str_Elements' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Elements], min(CASE when [GEODETAILS].[NAME] = 'Str_Intensity' 
then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Intensity], min(CASE when [GEODETAILS].[NAME] = 'Str_Appearance' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Appearance], min(CASE when [GEODETAILS].[NAME] = 'Str_Type' then [GEODETAILS].[VALUE] ELSE NULL END) as [Str_Type] FROM [GEODETAILS]
 WHERE [GEODETAILS].[NAME] IN ('Str_AlphaAngleCoreFr', 'Str_AlphaAngleCoreTo', 'Str_Beta', 'Str_Elements', 'Str_Intensity', 'Str_Appearance', 'Str_Type') GROUP BY [GEODETAILS].[PROJECTCODE], [GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] 
  ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN 
 (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], min(CASE when [GEOBIGCOMMENT].[NAME] = 'Struc_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Struc_Comments] FROM [GEOBIGCOMMENT] 
 WHERE [GEOBIGCOMMENT].[NAME] IN ('Struc_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] 
 AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Str_AlphaAngleCoreFr] IS NOT NULL OR [Str_AlphaAngleCoreTo] IS NOT NULL
  OR [Str_Beta] IS NOT NULL OR [Str_Elements] IS NOT NULL OR [Str_Intensity] IS NOT NULL OR [Str_Appearance] IS NOT NULL OR [Str_Type] IS NOT NULL OR [Struc_Comments] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) AS [TMPVIEW267]) [TMPSQLSHEETVIEW] ) s 
                             Where a.holeid = s.holeid  
                               and a.geolto>s.geolfrom and a.geolfrom<s.geolto and a.GEOLFROM<=s.GEOLFROM ) struc
						 on rn_sec = rn_struc
						 full join
					        (Select geolfrom as geolfrom_min, geolto as geolto_min,m.Mineralization_Zone_rus_Desc_D  as 'Mineralisation',
                                   row_number() over (order by geolfrom) as rn_min
                              From (
							  SELECT TOP 100 PERCENT * FROM (SELECT * FROM (SELECT TOP 100 PERCENT * FROM (SELECT TOP 100 PERCENT [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY],  ( CASE WHEN [ACQDERIVEDVIEW].[Mineralization_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Mineralization_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Occurence_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_IntensityPct]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Min_IntensityPct]) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization2_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization2_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Occurence_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_IntensityPct]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Min2_IntensityPct]) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization3_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization3_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Morphology_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Morphology_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Occurence_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Occurence_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_IntensityPct]) is null THEN '' ELSE ', ' + str([ACQDERIVEDVIEW].[Min3_IntensityPct]) + '%'
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization4_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization4_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Mineralization5_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Mineralization5_Desc_D]
END )  AS [Mineralization_Zone_Desc_D],  ( CASE WHEN [ACQDERIVEDVIEW].[Mineralization_rus_Desc_D] is null THEN '' ELSE [ACQDERIVEDVIEW].[Mineralization_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min_SizeTo])) + 'мм'
END +


CASE WHEN [ACQDERIVEDVIEW].[Min_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min_IntensityPct],4,1)) + '%'
END +




CASE WHEN [ACQDERIVEDVIEW].[Mineralization2_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization2_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min2_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min2_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min2_SizeTo])) + 'мм'
END +



CASE WHEN [ACQDERIVEDVIEW].[Min2_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min2_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min2_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min2_IntensityPct],4,1)) + '%'
END +


CASE WHEN [ACQDERIVEDVIEW].[Mineralization3_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization3_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min3_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min3_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min3_SizeTo])) + 'мм'
END +


CASE WHEN [ACQDERIVEDVIEW].[Min3_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min3_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min3_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min3_IntensityPct],4,1)) + '%'
END +




CASE WHEN [ACQDERIVEDVIEW].[Mineralization4_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization4_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Min4_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min4_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min4_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min4_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min4_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min4_SizeTo])) + 'мм'
END +

CASE WHEN [ACQDERIVEDVIEW].[Min4_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min4_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min4_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min4_IntensityPct],4,1)) + '%'
END +






CASE WHEN [ACQDERIVEDVIEW].[Mineralization5_rus_Desc_D] is null THEN '' ELSE '. ' + [ACQDERIVEDVIEW].[Mineralization5_rus_Desc_D]
END +

CASE WHEN [ACQDERIVEDVIEW].[Min5_Morphology_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min5_Morphology_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min5_SizeFrom]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min5_SizeFrom]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min5_SizeTo]) is null THEN '' ELSE ' - ' + ltrim(str([ACQDERIVEDVIEW].[Min5_SizeTo])) + 'мм'
END +

CASE WHEN [ACQDERIVEDVIEW].[Min5_Occurence_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Min5_Occurence_rus_Desc_D]
END +
CASE WHEN str([ACQDERIVEDVIEW].[Min5_IntensityPct]) is null THEN '' ELSE ', ' + ltrim(str([ACQDERIVEDVIEW].[Min5_IntensityPct],4,1)) + '%'
END +





CASE WHEN [ACQDERIVEDVIEW].[Min_Comments] is null THEN '' ELSE ', (' + [ACQDERIVEDVIEW].[Min_Comments] + ')'
END )  AS [Mineralization_Zone_rus_Desc_D] FROM ( SELECT TOP 100 PERCENT  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Morphology'
and LOOKUP = [DFINDF].[Min_Morphology]
and ACTIVE = 1 )  AS [Min_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Occurence'
and LOOKUP = [DFINDF].[Min_Occurence]
and ACTIVE = 1 )  AS [Min_Occurence_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Morphology'
and LOOKUP = [DFINDF].[Min2_Morphology]
and ACTIVE = 1 )  AS [Min2_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Occurence'
and LOOKUP = [DFINDF].[Min2_Occurence]
and ACTIVE = 1 )  AS [Min2_Occurence_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Morphology'
and LOOKUP = [DFINDF].[Min3_Morphology]
and ACTIVE = 1 )  AS [Min3_Morphology_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Occurence'
and LOOKUP = [DFINDF].[Min3_Occurence]
and ACTIVE = 1 )  AS [Min3_Occurence_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation'
and LOOKUP = [DFINDF].[Mineralisation]
and ACTIVE = 1 )  AS [Mineralization_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation2'
and LOOKUP = [DFINDF].[Mineralisation2]
and ACTIVE = 1 )  AS [Mineralization2_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation3'
and LOOKUP = [DFINDF].[Mineralisation3]
and ACTIVE = 1 )  AS [Mineralization3_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation4'
and LOOKUP = [DFINDF].[Mineralisation4]
and ACTIVE = 1 )  AS [Mineralization4_Desc_D],  ( select UPPER(LEFT(DESCRIPTION,1))+LOWER(STUFF(DESCRIPTION,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation5'
and LOOKUP = [DFINDF].[Mineralisation5]
and ACTIVE = 1 )  AS [Mineralization5_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Morphology'
and LOOKUP = [DFINDF].[Min_Morphology]
and ACTIVE = 1 )  AS [Min_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min_Occurence'
and LOOKUP = [DFINDF].[Min_Occurence]
and ACTIVE = 1 )  AS [Min_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Morphology'
and LOOKUP = [DFINDF].[Min2_Morphology]
and ACTIVE = 1 )  AS [Min2_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min2_Occurence'
and LOOKUP = [DFINDF].[Min2_Occurence]
and ACTIVE = 1 )  AS [Min2_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Morphology'
and LOOKUP = [DFINDF].[Min3_Morphology]
and ACTIVE = 1 )  AS [Min3_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min3_Occurence'
and LOOKUP = [DFINDF].[Min3_Occurence]
and ACTIVE = 1 )  AS [Min3_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min4_Morphology'
and LOOKUP = [DFINDF].[Min4_Morphology]
and ACTIVE = 1 )  AS [Min4_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min4_Occurence'
and LOOKUP = [DFINDF].[Min4_Occurence]
and ACTIVE = 1 )  AS [Min4_Occurence_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min5_Morphology'
and LOOKUP = [DFINDF].[Min5_Morphology]
and ACTIVE = 1 )  AS [Min5_Morphology_rus_Desc_D],  ( select LOWER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Min5_Occurence'
and LOOKUP = [DFINDF].[Min5_Occurence]
and ACTIVE = 1 )  AS [Min5_Occurence_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation'
and LOOKUP = [DFINDF].[Mineralisation]
and ACTIVE = 1 )  AS [Mineralization_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation2'
and LOOKUP = [DFINDF].[Mineralisation2]
and ACTIVE = 1 )  AS [Mineralization2_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation3'
and LOOKUP = [DFINDF].[Mineralisation3]
and ACTIVE = 1 )  AS [Mineralization3_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation4'
and LOOKUP = [DFINDF].[Mineralisation4]
and ACTIVE = 1 )  AS [Mineralization4_rus_Desc_D],  ( select UPPER(LEFT(Reportdesc,1))+LOWER(STUFF(Reportdesc,1,1,'')) from QV_VS_LOOKUP_RO
where FIELDTABLE = 'GEOLOGYCODESECONDARY'
and FIELDNAME = 'Mineralisation5'
and LOOKUP = [DFINDF].[Mineralisation5]
and ACTIVE = 1 )  AS [Mineralization5_rus_Desc_D], [HOLEID], [PROJECTCODE], [GEOLFROM], [GEOLTO], [PRIORITY], [Min_IntensityPct], [Min2_IntensityPct], [Min3_IntensityPct], [Min4_SizeFrom], [Min2_SizeTo], [Min5_SizeTo], [Min_Comments], [Min5_SizeFrom], [Min_SizeTo], [Min2_SizeFrom], [Min3_SizeTo],
 [Min4_IntensityPct], [Min5_IntensityPct], [Min_SizeFrom], [Min3_SizeFrom], [Min4_SizeTo] FROM  (  SELECT [GEOINTERVAL].[HOLEID], [GEOINTERVAL].[PROJECTCODE], [GEOINTERVAL].[GEOLFROM], [GEOINTERVAL].[GEOLTO], [GEOINTERVAL].[PRIORITY], CAST([GEODETAILS].[Min_IntensityPct] AS FLOAT) AS [Min_IntensityPct], 
 [GEODETAILS].[Min_Morphology], [GEODETAILS].[Min_Occurence], CAST([GEODETAILS].[Min2_IntensityPct] AS FLOAT) AS [Min2_IntensityPct], [GEODETAILS].[Min2_Morphology], [GEODETAILS].[Min2_Occurence], CAST([GEODETAILS].[Min3_IntensityPct] AS FLOAT) AS [Min3_IntensityPct], [GEODETAILS].[Min3_Morphology], 
 [GEODETAILS].[Min3_Occurence], [GEODETAILS].[Mineralisation], [GEODETAILS].[Mineralisation2], [GEODETAILS].[Mineralisation3], [GEODETAILS].[Mineralisation4], [GEODETAILS].[Mineralisation5], [GEOBIGCOMMENT].[Min_Comments], CAST([GEODETAILS].[Min_SizeFrom] AS FLOAT) AS [Min_SizeFrom], CAST([GEODETAILS].[Min_SizeTo] 
 AS FLOAT) AS [Min_SizeTo], CAST([GEODETAILS].[Min2_SizeFrom] AS FLOAT) AS [Min2_SizeFrom], CAST([GEODETAILS].[Min2_SizeTo] AS FLOAT) AS [Min2_SizeTo], CAST([GEODETAILS].[Min3_SizeFrom] AS FLOAT) AS [Min3_SizeFrom], CAST([GEODETAILS].[Min3_SizeTo] AS FLOAT) AS [Min3_SizeTo], CAST([GEODETAILS].[Min4_IntensityPct] 
 AS FLOAT) AS [Min4_IntensityPct], [GEODETAILS].[Min4_Morphology], [GEODETAILS].[Min4_Occurence], CAST([GEODETAILS].[Min4_SizeFrom] AS FLOAT) AS [Min4_SizeFrom], CAST([GEODETAILS].[Min4_SizeTo] AS FLOAT) AS [Min4_SizeTo], CAST([GEODETAILS].[Min5_IntensityPct] AS FLOAT) AS [Min5_IntensityPct],
  [GEODETAILS].[Min5_Morphology], [GEODETAILS].[Min5_Occurence], CAST([GEODETAILS].[Min5_SizeFrom] AS FLOAT) AS [Min5_SizeFrom], CAST([GEODETAILS].[Min5_SizeTo] AS FLOAT) AS [Min5_SizeTo] FROM [GEOINTERVAL] INNER JOIN (SELECT * FROM [HOLELOCATION]) AS [CollarWSF] ON [CollarWSF].[HOLEID] = [GEOINTERVAL].[HOLEID] 
  AND [CollarWSF].[PROJECTCODE] = [GEOINTERVAL].[PROJECTCODE] AND [CollarWSF].[HOLETYPE] = 'DRILLHOLE' LEFT JOIN (SELECT [GEODETAILS].[HOLEID], [GEODETAILS].[PROJECTCODE], [GEODETAILS].[GEOLFROM], [GEODETAILS].[GEOLTO], [GEODETAILS].[PRIORITY], min(CASE when [GEODETAILS].[NAME] = 'Min_IntensityPct' then
   CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min_Occurence], 
   min(CASE when [GEODETAILS].[NAME] = 'Min2_IntensityPct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min2_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min2_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min2_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min2_Occurence' 
   then [GEODETAILS].[VALUE] ELSE NULL END) as [Min2_Occurence], min(CASE when [GEODETAILS].[NAME] = 'Min3_IntensityPct' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min3_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min3_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min3_Morphology], 
   min(CASE when [GEODETAILS].[NAME] = 'Min3_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min3_Occurence], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation2' then [GEODETAILS].[VALUE] ELSE
    NULL END) as [Mineralisation2], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation3' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation3], min(CASE when [GEODETAILS].[NAME] = 'Mineralisation4' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation4], min(CASE when 
	[GEODETAILS].[NAME] = 'Mineralisation5' then [GEODETAILS].[VALUE] ELSE NULL END) as [Mineralisation5], min(CASE when [GEODETAILS].[NAME] = 'Min_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min_SizeTo' then CAST([GEODETAILS].[VALUE] 
	AS FLOAT) ELSE NULL END) as [Min_SizeTo], min(CASE when [GEODETAILS].[NAME] = 'Min2_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min2_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min2_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min2_SizeTo], 
	min(CASE when [GEODETAILS].[NAME] = 'Min3_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min3_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min3_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min3_SizeTo], min(CASE when [GEODETAILS].[NAME] = 'Min4_IntensityPct' 
	then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min4_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min4_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min4_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min4_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min4_Occurence], 
	min(CASE when [GEODETAILS].[NAME] = 'Min4_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min4_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min4_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min4_SizeTo], min(CASE when [GEODETAILS].[NAME] = 'Min5_IntensityPct' 
	then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min5_IntensityPct], min(CASE when [GEODETAILS].[NAME] = 'Min5_Morphology' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min5_Morphology], min(CASE when [GEODETAILS].[NAME] = 'Min5_Occurence' then [GEODETAILS].[VALUE] ELSE NULL END) as [Min5_Occurence],
	min(CASE when [GEODETAILS].[NAME] = 'Min5_SizeFrom' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min5_SizeFrom], min(CASE when [GEODETAILS].[NAME] = 'Min5_SizeTo' then CAST([GEODETAILS].[VALUE] AS FLOAT) ELSE NULL END) as [Min5_SizeTo] FROM [GEODETAILS] GROUP BY [GEODETAILS].[PROJECTCODE], 
	[GEODETAILS].[PRIORITY], [GEODETAILS].[GEOLTO], [GEODETAILS].[GEOLFROM], [GEODETAILS].[HOLEID])[GEODETAILS] ON [GEOINTERVAL].[PROJECTCODE] = [GEODETAILS].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEODETAILS].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEODETAILS].[GEOLTO] 
		AND [GEOINTERVAL].[GEOLFROM] = [GEODETAILS].[GEOLFROM] AND [GEOINTERVAL].[HOLEID] = [GEODETAILS].[HOLEID] LEFT JOIN (SELECT [GEOBIGCOMMENT].[HOLEID], [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[GEOLTO], [GEOBIGCOMMENT].[PRIORITY], 
		min(CASE when [GEOBIGCOMMENT].[NAME] = 'Min_Comments' then [GEOBIGCOMMENT].[VALUE] ELSE NULL END) as [Min_Comments] FROM [GEOBIGCOMMENT] WHERE [GEOBIGCOMMENT].[NAME] IN ('Min_Comments') GROUP BY [GEOBIGCOMMENT].[PROJECTCODE], [GEOBIGCOMMENT].[PRIORITY], [GEOBIGCOMMENT].[GEOLTO], 
		[GEOBIGCOMMENT].[GEOLFROM], [GEOBIGCOMMENT].[HOLEID])[GEOBIGCOMMENT] ON [GEOINTERVAL].[PROJECTCODE] = [GEOBIGCOMMENT].[PROJECTCODE] AND [GEOINTERVAL].[PRIORITY] = [GEOBIGCOMMENT].[PRIORITY] AND [GEOINTERVAL].[GEOLTO] = [GEOBIGCOMMENT].[GEOLTO] AND [GEOINTERVAL].[GEOLFROM] = [GEOBIGCOMMENT].[GEOLFROM] 
		AND [GEOINTERVAL].[HOLEID] = [GEOBIGCOMMENT].[HOLEID] WHERE ([Min_IntensityPct] IS NOT NULL OR [Min_Morphology] IS NOT NULL OR [Min_Occurence] IS NOT NULL OR [Min2_IntensityPct] IS NOT NULL OR [Min2_Morphology] IS NOT NULL OR [Min2_Occurence] IS NOT NULL OR [Min3_IntensityPct] IS NOT NULL 
		OR [Min3_Morphology] IS NOT NULL OR [Min3_Occurence] IS NOT NULL OR [Mineralisation] IS NOT NULL OR [Mineralisation2] IS NOT NULL OR [Mineralisation3] IS NOT NULL OR [Mineralisation4] IS NOT NULL OR [Mineralisation5] IS NOT NULL OR [Min_Comments] IS NOT NULL OR [Min_SizeFrom] IS NOT NULL 
		OR [Min_SizeTo] IS NOT NULL OR [Min2_SizeFrom] IS NOT NULL OR [Min2_SizeTo] IS NOT NULL OR [Min3_SizeFrom] IS NOT NULL OR [Min3_SizeTo] IS NOT NULL OR [Min4_IntensityPct] IS NOT NULL OR [Min4_Morphology] IS NOT NULL OR [Min4_Occurence] IS NOT NULL OR [Min4_SizeFrom] IS NOT NULL OR [Min4_SizeTo] IS NOT NULL 
		OR [Min5_IntensityPct] IS NOT NULL OR [Min5_Morphology] IS NOT NULL OR [Min5_Occurence] IS NOT NULL OR [Min5_SizeFrom] IS NOT NULL OR [Min5_SizeTo] IS NOT NULL)  ) [DFINDF]) [ACQDERIVEDVIEW]) [ACQTMP]  ) AS [TMPVIEW275]) [TMPSQLSHEETVIEW]) m 
                             Where a.holeid = m.holeid  
                               and a.geolto>m.geolfrom and a.geolfrom<m.geolto and a.GEOLFROM<=m.GEOLFROM) min on isnull(rn_sec,rn_alt) = rn_min
) others
left join (select distinct case row_number() over(partition by holeid order by geolfrom) when 1 then holeid end as x_holeid, * from GEODETAILS where name='Lith_RockName' or name='Alteration' or name='Mineralization' or name='Structure'
)ord on ord.holeid=a.HOLEID and ord.GEOLFROM=a.GEOLFROM and ord.GEOLTO=a.GEOLTO
WHere a.HOLEID='DZDZ-18-001'
order by ord.HOLEID,ord.GEOLFROM

--Order by a.holeid desc,a.geolfrom,a.Primary_Lithology_rus_Desc_D, COALESCE(others.rn_struc,others.rn_sec,others.rn_alt,others.rn_min) asc


К сообщению приложен файл. Размер - 147Kb
16 июл 18, 09:26    [21573201]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
aleks222
Member

Откуда:
Сообщений: 957
TaPaK
запрос, кончно, впечатляет...

До центра Земли.

Но такая портянка, скорее, говорит о крайне низкой квалификации запросописателя.
16 июл 18, 09:27    [21573209]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
TaPaK, дык мы же всем форумом его писали, помнишь? Ты, Добряк, Копели))))
16 июл 18, 09:27    [21573210]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
aleks222, а я и не спорю совсем. Я первый год изучаю sql. Ну как изучаю... в рабочем порядке...в срочном рабочем порядке...пипец просто
16 июл 18, 09:28    [21573214]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
aleks222
Member

Откуда:
Сообщений: 957
katish444
aleks222,
Ты не мог бы подсказать команду, которая запретила бы нумерации дублироваться


Это очень просто - напиши запрос, который "не размножает" строки.
Ибо сама нумерация не способна "дублироваться".
16 июл 18, 09:29    [21573216]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
katish444,

длинный запрос написали....

автор
я не могу на это влиять, работаю с тем, что дают. Это не в моей компетенции.

что вам мешает забирать то что есть, нормализировать и строить свои отчёты без этой жести под спойлером
16 июл 18, 09:29    [21573217]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
aleks222,
тогда они идут не по порядку(((
16 июл 18, 09:29    [21573218]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
TaPaK, компьютерная безграмотность мешает
16 июл 18, 09:30    [21573220]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
aleks222,
left join (select distinct case row_number() over(partition by holeid order by geolfrom) when 1 then holeid end as x_holeid, * from GEODETAILS where name='Lith_RockName' or name='Alteration' or name='Mineralization' or name='Structure'
)ord on ord.holeid=a.HOLEID and ord.GEOLFROM=a.GEOLFROM and ord.GEOLTO=a.GEOLTO

я дописала эту строку и началось дублирование
16 июл 18, 09:31    [21573225]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
aleks222
Member

Откуда:
Сообщений: 957
katish444
aleks222,
тогда они идут не по порядку(((


Порядок в запросе SQL обеспечивается кляузой ORDER BY.
16 июл 18, 09:32    [21573226]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
aleks222, я знаю, я использовала эту функцию
16 июл 18, 09:36    [21573236]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
katish444
aleks222,
left join (select distinct case row_number() over(partition by holeid order by geolfrom) when 1 then holeid end as x_holeid, * from GEODETAILS where name='Lith_RockName' or name='Alteration' or name='Mineralization' or name='Structure'
)ord on ord.holeid=a.HOLEID and ord.GEOLFROM=a.GEOLFROM and ord.GEOLTO=a.GEOLTO


я дописала эту строку и началось дублирование
Значит, сочетание holeid, GEOLFROM и GEOLTO неуникально
Отладьте отдельно этот запрос, добейтесь уникальности записей, запрос то несвязанный с другими, его можно отдельно запустить
Сразу бросается в глаза то, что вы вычисляете x_holeid, но никак его не используете. Видимо, его нужно использовать в джойне вместо holeid?
В общем, просто отладьте этот запрос.
16 июл 18, 09:37    [21573242]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
alexeyvg,
спасибо, сейчас буду пытаться
16 июл 18, 09:40    [21573248]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
katish444,
Order By ....., COALESCE(geolfrom_sec,geolfrom_str,geolfrom_alt,geolfrom_min) asc
16 июл 18, 09:43    [21573256]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
Kopelly, это уже есть, оно сортирует правильно, но не относительно основного интервала
16 июл 18, 09:48    [21573261]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
Kopelly, подожди-ка....
16 июл 18, 09:49    [21573267]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
Kopelly
katish444,
Order By ....., COALESCE(geolfrom_sec,geolfrom_str,geolfrom_alt,geolfrom_min) asc

Ничего не изменилось.... о, горе мне ((
16 июл 18, 09:50    [21573270]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
Kopelly, а есть еще варианты?
16 июл 18, 09:57    [21573280]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
katish444,
Я имел в виду так:
Order by a.holeid desc,a.geolfrom,a.Primary_Lithology_rus_Desc_D, COALESCE(geolfrom_sec,geolfrom_str,geolfrom_alt,geolfrom_min)
16 июл 18, 09:59    [21573287]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста, исправить ошибку отображения/сортировки COALESCE  [new]
katish444
Member

Откуда:
Сообщений: 261
Kopelly
katish444,
Я имел в виду так:
Order by a.holeid desc,a.geolfrom,a.Primary_Lithology_rus_Desc_D, COALESCE(geolfrom_sec,geolfrom_str,geolfrom_alt,geolfrom_min)

Сейчас буду пробовать, спасибо большое
16 июл 18, 10:03    [21573302]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить