select DRILLINGDETAIL.holeid, DRILLINGDETAIL.projectcode,  T3.GeolLength, t4.[Sample],drillingdate,shift, 
 (case when shift = 'day' or shift = 'unknown'  and DRILLINGDETAIL.name = 'totaldrilldepth' then DRILLINGDETAIL.Value  end)[DAY],
  (case when shift = 'night' and DRILLINGDETAIL.name = 'totaldrilldepth' then DRILLINGDETAIL.Value  end)[NIGHT],
 
  TotalDrillDepth3 from   (

select geointerval.holeid, (max(geolto) - min(geolfrom))GeolLength from GEOINTERVAL left join DRILLINGDETAIL on GEOINTERVAL.holeid = DRILLINGDETAIL.holeid    group by GEOINTERVAL.holeid)T3

left join 

 ( select Holeid, count(SampleID) as [Sample] from sample where  SampleID<>holeid group by holeid)t4
 on t4.HOLEID=t3.HOLEID
 left join (select holeid, TotalDrillDepth3 = SUM(Cast((case when ddd.name = 'totaldrilldepth' then ddd.Value end) as DECIMAL(20,2))) from DRILLINGDETAIL ddd group by HOLEID)dd on dd.holeid = t3.holeid
 left join DRILLINGDETAIL on DRILLINGDETAIL.holeid=t3.HOLEID 

 where DRILLINGDETAIL.projectcode ='uz' and cast(DRILLINGDETAIL.DRILLINGDATE as date)  between '27-oct-2017' and '27-oct-2017'  

 group by DRILLINGDETAIL.holeid,DRILLINGDETAIL.projectcode,  T3.GeolLength, t4.[Sample],shift,DRILLINGDATE,DRILLINGDETAIL.name,DRILLINGDETAIL.Value, TotalDrillDepth3,shift[/SUP]