SELECT  TOP 100 PERCENT DRILLINGDETAIL.HOLEID,
case row_number() over(partition by DRILLINGDETAIL.holeid order by tab3.geol_from) when 1 then DRILLINGDETAIL.HOLEID end as x_holeid,
DRILLINGDETAIL.DRILLINGDATE,   DRILLINGDETAIL.PROJECTCODE,  Cast(max(tab3.geol_from) as float)as GeolFrom, max(tab3.geol_to)GeolTo, 
 tab3.[Primary Lithology] ,EventFromDAY,EventTODAY,EventFromNight,EventTOnight
 
					FROM 
					DRILLINGDETAIL

					left join

					(Select holeid,
					 max(   case
				   when [DRILLINGDETAIL].[NAME] = 'EventFrom' and [Shift]='day' then
					   [DRILLINGDETAIL].[VALUE]
				   else
					   ''
			   end)EventFromDAY 
			   ,max(   case
				   when [DRILLINGDETAIL].[NAME] = 'Eventto'  and [Shift]='day'then
					   [DRILLINGDETAIL].[VALUE]
				   else
					   ''
			   end)EventTODAY
			 ,
			 max(   case
				   when [DRILLINGDETAIL].[NAME] = 'EventFrom' and [Shift]='night' then
					   [DRILLINGDETAIL].[VALUE]
				   else
					   ''
			   end)EventFromnight
			   ,max(   case
				   when [DRILLINGDETAIL].[NAME] = 'Eventto'  and [Shift]='night'then
					   [DRILLINGDETAIL].[VALUE]
				   else
					   ''
			   end)EventTOnight 
					FROM 
					DRILLINGDETAIL
					group by HOLEID)tab8 on tab8.HOLEID=DRILLINGDETAIL.HOLEID

LEFT Join 


 ( select holeid,
 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'



 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

)tab3 on


 tab3.holeid=DRILLINGDETAIL.HOLEID where [DRILLINGDETAIL].[PROJECTCODE] = 'ma' 
 ---and x_holeid='makh-17-005'
 cast(DRILLINGDETAIL.DRILLINGDATE as datetime)= '15-jul-2017' and geol_from<=(cast(EventFromDAY as float))<=geol_to

 group by   DRILLINGDETAIL.HOLEID, DRILLINGDETAIL.PROJECTCODE, tab3.[Primary Lithology] ,DRILLINGDETAIL.DRILLINGDATE,tab3.geol_from,tab3.geol_to,tab3.x_holeid ,tab3.holeid,EventFromDAY ,EventTODAY,EventFromNight,EventTOnight
 
 ORDER BY cast(DRILLINGDETAIL.DRILLINGDATE as datetime),tab3.holeid,tab3.x_holeid desc,geol_from