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

Откуда:
Сообщений: 261
Ребята, здравствуйте
Подскажите, пожалуйста как сделать сортировку внутри подзапроса
На днях я для примера создавала запрос и с временной таблицей все работает - все по порядку сортировка не нужна
Но когда я вставила в запрос, свой подзапрос с реальными данными, то данные не сортируются. Когда пытаюсь впихнуть туда Order by,
то мне выдает надпись
"Msg 1033, Level 15, State 1, Line 538
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Msg 102, Level 15, State 1, Line 798
Incorrect syntax near 'min'."

Как только убираю сортировку, скрипт выполняется, но значения не попорядку





Кажется у меня кривые руки...

К сообщению приложен файл. Размер - 117Kb
2 фев 18, 08:46    [21159525]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
katish444
Member

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


+
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,geolfrom_str, geolto_str, Strucrure, geolfrom_alt,geolto_alt,Alteration,geolfrom_min,geolto_min,Mineralisation 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 ', ' + 'óã.áåòòà(top) ' + 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 ', ' + 'óã.áåòòà(top) ' + 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) 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 ', ' + 'óã.áåòòà(top) ' + 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 ) 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) min on isnull(rn_sec,rn_alt) = rn_min
) others
WHere holeid='kech-16-004'

Модератор: В следующий раз подобную портянку не под спойлером удалю на месте


Сообщение было отредактировано: 2 фев 18, 11:10
2 фев 18, 08:47    [21159528]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
Добрый Э - Эх
Guest
katish444,

сортируй на самом высоком уровне запроса. А если нужно именно в подзапросе, то прикрути TOP 100000000 :)

+ Если выложил портянку - спойлер сделать не забудь
И, это, - портянки такие научись прятать в спойлер :)
2 фев 18, 08:50    [21159531]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
Добрый Э - Эх
Guest
+ Так могло бы выглядеть твое сообщение :)
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,geolfrom_str, geolto_str, Strucrure, geolfrom_alt,geolto_alt,Alteration,geolfrom_min,geolto_min,Mineralisation 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] + ' &#246;&#226;&#229;&#242;, '
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] + ' - &#226;&#242;&#238;&#240;&#238;&#241;&#242;&#229;&#239;&#229;&#237;&#237;&#251;&#233; &#246;&#226;&#229;&#242; '
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith1_Texture_rus_Desc_D] is null THEN '' ELSE '&#241;&#242;&#240;&#243;&#234;&#242;&#243;&#240;&#224; ' + [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 ', &#242;&#229;&#234;&#241;&#242;&#243;&#240;&#224; ' + [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 ', &#246;&#226;&#229;&#242; ' + [ACQDERIVEDVIEW].[Lith2_Colour_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Texture_rus_Desc_D] is null THEN '' ELSE ', &#241;&#242;&#240;&#243;&#234;&#242;&#243;&#240;&#224; ' + [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 ', &#242;&#229;&#234;&#241;&#242;&#243;&#240;&#224; ' + [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] + ' &#246;&#226;&#229;&#242;'
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Texture_Desc_D] is null THEN '' ELSE ', &#241;&#242;&#240;&#243;&#234;&#242;&#243;&#240;&#224; ' + [ACQDERIVEDVIEW].[Lith3_Texture_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Lith3_Structure_rus_Desc_D] is null THEN '' ELSE ', &#242;&#229;&#234;&#241;&#242;&#243;&#240;&#224; ' + [ACQDERIVEDVIEW].[Lith3_Structure_rus_Desc_D]
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Top_Type] is null THEN '' ELSE '. &#194;&#229;&#240;&#245;&#237;&#232;&#233; &#234;&#238;&#237;&#242;&#224;&#234;&#242;: ' + [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 ', ' + '&#243;&#227;.&#238;&#234; ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Top_Alpha]))
END +
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Top_Beta]) is null THEN '' ELSE ', ' + '&#243;&#227;.&#225;&#229;&#242;&#242;&#224;(top) ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Top_Beta]))
END +
CASE WHEN [ACQDERIVEDVIEW].[Str2_Bot_Type] is null THEN '' ELSE '. &#205;&#232;&#230;&#237;&#232;&#233; &#234;&#238;&#237;&#242;&#224;&#234;&#242;: ' + [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 ', ' + '&#243;&#227;.&#238;&#234; ' + ltrim(str([ACQDERIVEDVIEW].[Str2_Bot_Alpha]))
END+
CASE WHEN str([ACQDERIVEDVIEW].[Str2_Bot_Beta]) is null THEN '' ELSE ', ' + '&#243;&#227;.&#225;&#229;&#242;&#242;&#224;(top) ' + 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] + ' &#232;&#237;&#242;&#229;&#237;&#241;&#232;&#226;&#237;&#238;&#241;&#242;&#252;'
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) 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 ' &#237;&#224; &#238;&#242;&#236;.' + str([ACQDERIVEDVIEW].[GEOLTO],7,2) + '&#236; &#239;&#238;&#228; &#243;&#227;.&#238;&#234; ' + ltrim(str([ACQDERIVEDVIEW].[Str_AlphaAngleCoreFr])) ELSE 
' &#243;&#227;.&#238;&#234; ' + 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 ', ' + '&#243;&#227;.&#225;&#229;&#242;&#242;&#224;(top) ' + 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 ) 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])) + '&#236;&#236;'
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])) + '&#236;&#236;'
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])) + '&#236;&#236;'
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])) + '&#236;&#236;'
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])) + '&#236;&#236;'
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) min on isnull(rn_sec,rn_alt) = rn_min
) others
WHere holeid='kech-16-004'
2 фев 18, 08:53    [21159535]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20223
А под спойлер убрать эту портянку, да в тег кода обернуть - не судьба? про форматирование я уж и не заикаюсь...

katish444
как сделать сортировку внутри подзапроса

Сортировка в подзапросе - не имеет смысла (если в нём нет ограничения на количество записей, конечно - но это отдельная песня). Даже если данные в подзапросе отсортированы, всё равно при использовании в основном запросе они будут перемешаны так, чтобы наиболее эффективно выполнить этот самый запрос. Сортировать необходимо конечный результат - порой для этого приходится в список вывода подзапроса вводить дополнительные поля, которые и будут использованы при сортировке в основном запросе.
2 фев 18, 08:54    [21159540]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20223
PS. И вот эта дикость:
+
CASE WHEN [ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D] is null THEN '' ELSE ', ' + [ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D]
END
прекрасно заменяется на
+ COALESCE([ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D],'')
2 фев 18, 08:56    [21159548]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20223
Пардон,

+ COALESCE(', ' + [ACQDERIVEDVIEW].[Lith2_Structure2_rus_Desc_D],'')
2 фев 18, 08:57    [21159552]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
katish444
Member

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

declare @GEODETAIL table (Holeid varchar(20), geolfrom float, geolto float, name varchar(20),value varchar(20))
insert into @GEODETAIL (holeid, geolfrom, geolto, name, value) values
('hr-1',0,8,'Lith_RockName','Песчанник'),
('hr-1',8,50,'Lith_RockName','Алевролит'),
('hr-1',50,100,'Lith_RockName','Известняк'),
('hr-1',100,108,'Lith_RockName','Гравелиты'),
('hr-1',108,200,'Lith_RockName','Алевролиты'),
('hr-1',0,5,'Sec_RockName','Глина'),
('hr-1',30,38,'Alt_RockName','Переслаивание'),
('hr-1',39,39,'Alt_RockName','Контакт'),
('hr-1',36,36,'Alt_RockName','Переслаивание'),
('hr-1',10,10.1,'Alt_RockName','Переслаивание'),
('hr-1',120,180,'Min_RockName','Минерализация'),
('hr-1',190,200,'Min_RockName','Минерализация'),
('hr-1',121,121,'Min_RockName','Минерализация')

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.value order by a.geolfrom) when 1 then a.VALUE end as Lith_RockNameee,
a.*,others.* from @GEODETAIL a
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, value as 'Sec_RockName',
row_number() over (order by geolfrom) as rn_sec
From @GEODETAIL b
Where a.holeid = b.holeid and b.name = 'Sec_RockName'
and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) Sec
full join
(Select geolfrom as geolfrom_alt, geolto as geolto_alt, value as 'Alt_RockName',
row_number() over (order by geolfrom) as rn_alt
From @GEODETAIL b
Where a.holeid = b.holeid and b.name = 'Alt_RockName'
and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) alt
on rn_sec = rn_alt
full join
(Select geolfrom as geolfrom_min, geolto as geolto_min, value as 'Min_RockName',
row_number() over (order by geolfrom) as rn_min
From @GEODETAIL b
Where a.holeid = b.holeid and b.name = 'Min_RockName'
and a.geolto>b.geolfrom and a.geolfrom<=b.geolto ) min
on isnull(rn_sec,rn_alt) = rn_min

) others
WHere a.name = 'Lith_RockName'


Вот тут не сортируется и не вставляется ORDER BY

К сообщению приложен файл. Размер - 108Kb
2 фев 18, 09:11    [21159572]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
katish444
Member

Откуда:
Сообщений: 261
Akina,
спасибо за совет
А как прятать?
2 фев 18, 09:12    [21159576]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
katish444
Member

Откуда:
Сообщений: 261
Привет.
А как прятать?
Получилось?
Добрый Э - Эх
katish444,

сортируй на самом высоком уровне запроса. А если нужно именно в подзапросе, то прикрути TOP 100000000 :)

+ Если выложил портянку - спойлер сделать не забудь
И, это, - портянки такие научись прятать в спойлер :)
2 фев 18, 09:13    [21159577]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
katish444
Member

Откуда:
Сообщений: 261
А поняла, то есть как бы я не сортировала в подзапросе, основной запрос сделает так как ему хочется

А как мне тогда поступить? Если я на высоком уровне вставлю ORDER BY, то он отсортирует с учетом пустых значений NULL
И как мне поступить?

Akina
А под спойлер убрать эту портянку, да в тег кода обернуть - не судьба? про форматирование я уж и не заикаюсь...

katish444
как сделать сортировку внутри подзапроса

Сортировка в подзапросе - не имеет смысла (если в нём нет ограничения на количество записей, конечно - но это отдельная песня). Даже если данные в подзапросе отсортированы, всё равно при использовании в основном запросе они будут перемешаны так, чтобы наиболее эффективно выполнить этот самый запрос. Сортировать необходимо конечный результат - порой для этого приходится в список вывода подзапроса вводить дополнительные поля, которые и будут использованы при сортировке в основном запросе.
2 фев 18, 09:15    [21159581]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
katish444
Member

Откуда:
Сообщений: 261
katish444, кажется на научилась
+
Прошу у всех прощения

2 фев 18, 09:17    [21159584]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
katish444
Member

Откуда:
Сообщений: 261
Akina
А под спойлер убрать эту портянку, да в тег кода обернуть - не судьба? про форматирование я уж и не заикаюсь...

katish444
как сделать сортировку внутри подзапроса

Сортировка в подзапросе - не имеет смысла (если в нём нет ограничения на количество записей, конечно - но это отдельная песня). Даже если данные в подзапросе отсортированы, всё равно при использовании в основном запросе они будут перемешаны так, чтобы наиболее эффективно выполнить этот самый запрос. Сортировать необходимо конечный результат - порой для этого приходится в список вывода подзапроса вводить дополнительные поля, которые и будут использованы при сортировке в основном запросе.


Вот временная таблица
+
declare @GEODETAIL table (Holeid varchar(20), geolfrom float, geolto float, name varchar(20),value varchar(20))
insert into @GEODETAIL (holeid, geolfrom, geolto, name, value) values
('hr-1',0,8,'Lith_RockName','Песчанник'),
('hr-1',8,50,'Lith_RockName','Алевролит'),
('hr-1',50,100,'Lith_RockName','Известняк'),
('hr-1',100,108,'Lith_RockName','Гравелиты'),
('hr-1',108,200,'Lith_RockName','Алевролиты'),
('hr-1',0,5,'Sec_RockName','Глина'),
('hr-1',30,38,'Alt_RockName','Переслаивание'),
('hr-1',39,39,'Alt_RockName','Контакт'),
('hr-1',36,36,'Alt_RockName','Переслаивание'),
('hr-1',10,10.1,'Alt_RockName','Переслаивание'),
('hr-1',120,180,'Min_RockName','Минерализация'),
('hr-1',190,200,'Min_RockName','Минерализация'),
('hr-1',121,121,'Min_RockName','Минерализация')

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.value order by a.geolfrom) when 1 then a.VALUE end as Lith_RockNameee,
a.*,others.* from @GEODETAIL a 
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, value as 'Sec_RockName',
                                   row_number() over (order by geolfrom) as rn_sec 
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Sec_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) Sec
					  full join 
					        (Select geolfrom as geolfrom_alt, geolto as geolto_alt, value as 'Alt_RockName',
                                   row_number() over (order by geolfrom) as rn_alt
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Alt_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) alt
						 on rn_sec = rn_alt
					  full join 
					        (Select geolfrom as geolfrom_min, geolto as geolto_min, value as 'Min_RockName',
                                   row_number() over (order by geolfrom) as rn_min
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Min_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto ) min
						 on isnull(rn_sec,rn_alt) = rn_min 
						 
) others
WHere a.name = 'Lith_RockName' 


Подскажи пожалуйста как отсортировать Min_RockName
2 фев 18, 09:21    [21159591]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
katish444,

Order by a.holeid,a.geolfrom,a.geolto,a.value, COALESCE(others.rn_sec,others.rn_alt,others.rn_min)
2 фев 18, 09:48    [21159648]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка внутри подзапроса  [new]
katish444
Member

Откуда:
Сообщений: 261
Класс! благодарю

Kopelly
katish444,

Order by a.holeid,a.geolfrom,a.geolto,a.value, COALESCE(others.rn_sec,others.rn_alt,others.rn_min)
2 фев 18, 10:33    [21159802]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить