SELECT CASE ROW_NUMBER() OVER(PARTITION BY pp.PROJECTCODE ORDER BY a.holeid)
           WHEN 1
           THEN pp.PROJECTCODE
       END AS x_project,
       CASE ROW_NUMBER() OVER(PARTITION BY pp.DESCRIPTION ORDER BY a.holeid)
           WHEN 1
           THEN pp.DESCRIPTION
       END AS x_project_desc,
       --pp.PROJECTCODE,pp.DESCRIPTION,
       a.HOLEID, 
       a.DrillLenght, 
       b.SendSample, 
       c.assay, 
       TakenSample, 
       [Depth_drill], 
       totalcut.Totalcutting, 
       geo2.toTOTAL2
FROM PROJECT pp
     LEFT JOIN
(
    SELECT dd.PROJECTCODE AS x_project, 
           dd.HOLEID, 
           SUM(CAST((CASE
                         WHEN name = 'totaldrilldepth'
                         THEN value
                     END) AS FLOAT)) AS DrillLenght
    FROM drillingdetail dd
    WHERE CAST([DRILLINGDATE] AS DATE) BETWEEN '20-jul-2018' AND '06-aug-2018'
    GROUP BY PROJECTCODE, 
             HOLEID
) a ON pp.PROJECTCODE = a.x_project
     LEFT JOIN
(
    SELECT s.PROJECTCODE AS x_project, 
           s.HOLEID, 
           COUNT(sd.sampleid) AS SendSample
    FROM SAMPLEDESPATCH sd
         LEFT JOIN DESPATCHSEND ds ON sd.DESPATCHNO = ds.DESPATCHNO
         LEFT JOIN sample s ON s.SAMPLEID = sd.SAMPLEID
    WHERE CAST([senddate] AS DATE) BETWEEN '01-aug-2018' AND '06-aug-2018'
    GROUP BY PROJECTCODE, 
             HOLEID
) b ON pp.PROJECTCODE = b.x_project
       AND a.HOLEID = b.HOLEID
     LEFT JOIN
(
    SELECT projectcode AS x_project, 
           holeid, 
           COUNT(sss) assay
    FROM
    (
        SELECT DISTINCT 
               s.sampleid AS sss, 
               PROJECTCODE, 
               HOLEID
        FROM cORPSAMPLEASSAY c
             LEFT JOIN sample s ON s.SAMPLEID = c.SAMPLEID
        WHERE CAST([LOADDATE] AS DATE) BETWEEN '01-aug-2018' AND '06-aug-2018'
    ) abc
    GROUP BY abc.PROJECTCODE, 
             abc.HOLEID
) c ON pp.PROJECTCODE = b.x_project
       AND b.HOLEID = c.HOLEID
     LEFT JOIN
(
    SELECT ss.PROJECTCODE AS x_project, 
           holeid, 
           COUNT(ts.sampleid) AS TakenSample
    FROM SAMPLEDETAILS ts
         LEFT JOIN sample ss ON ss.SAMPLEID = ts.SAMPLEID
    WHERE CAST((CASE
                    WHEN name = 'SampDateTaken'
                    THEN value
                END) AS DATE) BETWEEN '01-aug-2018' AND '06-aug-2018'
    GROUP BY ss.PROJECTCODE, 
             ss.HOLEID
) t ON pp.PROJECTCODE = t.x_project
       AND t.HOLEID = a.HOLEID
     LEFT JOIN
(
    SELECT projectcode AS x_project, 
           holeid, 
           SUM([Depth_dh]) AS [Depth_drill]
    FROM
    (
        SELECT sample.projectcode, 
               sample.holeid, 
               ROUND(MAX(sampto) - MIN(SAMPFROM), 2) AS [Depth_dh]
        FROM sample
             LEFT JOIN SAMPLEDETAILS ON sample.SAMPLEID = SAMPLEDETAILS.sampleid
        WHERE CAST((CASE
                        WHEN name = 'SampDateTaken'
                        THEN value
                    END) AS DATE) BETWEEN '01-aug-2018' AND '06-aug-2018'
        GROUP BY sample.sampleid, 
                 projectcode, 
                 HOLEID
    ) zz
    GROUP BY projectcode, 
             HOLEID
) z ON pp.PROJECTCODE = z.x_project
       AND z.holeid = b.HOLEID
     LEFT JOIN
(
    SELECT DISTINCT 
           holeid, 
           projectcode
    FROM holelocation
) hl ON hl.HOLEID = a.HOLEID
     LEFT JOIN
(
    SELECT DISTINCT 
           holeid, 
           Totalcutting = MAX(geolto) - MIN(geolfrom)
    FROM GEODETAILS
    WHERE name = 'Cutting_Date'
          AND CAST(VALUE AS DATE) BETWEEN '01-aug-2018' AND '06-aug-2018'
    GROUP BY holeid
) totalcut ON totalcut.HOLEID = hl.HOLEID
     LEFT JOIN
(
    SELECT holeid hole1, 
           ROUND((MAX(geolto) - MIN(geolfrom)), 2) toDAY1
    FROM GEODETAILS
    WHERE value BETWEEN '01-aug-2018' AND '06-aug-2018'
          AND name NOT LIKE '%core%'
          AND name NOT LIKE '%rqd%'
          AND name NOT LIKE '%cut%'
    GROUP BY holeid, 
             value
) geo1 ON geo1.hole1 = hl.HOLEID
     LEFT JOIN
(
    SELECT holeid hole2, 
           MAX(geolto) toTOTAL2
    FROM GEODETAILS
    WHERE name = 'Lith_RockName'
    GROUP BY holeid
) geo2 ON geo2.hole2 = hl.HOLEID
WHERE a.x_project IS NOT NULL
      OR b.x_project IS NOT NULL
      OR c.x_project IS NOT NULL
      OR --x.x_project is not null or
      t.x_project IS NOT NULL
      OR z.x_project IS NOT NULL
ORDER BY pp.DESCRIPTION;