-- Type your SQL script here.
    select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid, 
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,[a].Primary_Lithology_rus_Desc_D order by a.geolfrom) when 1 then [a].Primary_Lithology_rus_Desc_D end as 'Primary Lithology',



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

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

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

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

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

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

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


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

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

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

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

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


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




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



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


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


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




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

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






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

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

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





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

-- Type your SQL script here.
    select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid, 
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,[a].Primary_Lithology_rus_Desc_D order by a.geolfrom) when 1 then [a].Primary_Lithology_rus_Desc_D end as 'Primary Lithology',



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

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

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

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

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

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

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


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

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

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

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

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


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




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



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


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


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




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

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






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

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

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





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

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