select  WORKORDERNO
       ,LOCATION
       ,WORKTYPE
       ,t3.[Work Type Description] as WORK_TYPE_DESCRIPTION
       ,WORK_SUBTYPE
       ,t2.[Sub Type Description] as SUB_TYPE_DESCRIPTION
       ,JOB_TYPE
       ,t.[Budget Process Description] AS PROCESS_NAME
       ,PROJECT_NUMBER
       ,SUM(ISNULL(CAST(COMPANYCOUNT AS INT),0)) AS COMPANY
       ,SUM(ISNULL(CAST(CONTRACTORCOUNT AS INT),0)) AS CONTRACTOR

FROM
(SELECT WORKORDERNO
      ,STATUS
      ,cast(STATUSDATE AS DATE) AS ACT_FINISH
      ,WORKTYPE
      ,WORK_SUBTYPE
      ,LOCATION
      ,concat(WORKTYPE,WORK_SUBTYPE) as JOB_TYPE
      ,ASSIGNED_LABOR
      ,CASE WHEN [ASSIGNED_LABOR] IN ('X','Y','Z') THEN '1' ELSE '0'
                                      END AS COMPANYCOUNT
      ,CASE WHEN [ASSIGNED_LABOR] IN ('X','Y','Z') THEN '0' ELSE '1'
                                      END AS CONTRACTORCOUNT
      ,SUBSTRING(ACCOUNT, CHARINDEX('-', ACCOUNT, charindex('-', ACCOUNT, CHARINDEX('-', ACCOUNT, CHARINDEX('-', ACCOUNT,
                CHARINDEX('-', ACCOUNT) +1 ) +1) +1) +1) +1, 13) AS PROJECT_NUMBER
     ,FIELD_REMARKS
FROM [TTT].[dbo].[Current _Status]
WHERE STATUS in ('COMP', 'FDCOMP', 'EBCOMP', 'EBERROR', 'FLN', 'FDCPERR')) A
left join [dbo].[REF_table] t on t.[Job Type] = A.JOB_TYPE
left join [dbo].[REF_table] t2 on t2.[Sub Type] = A.WORK_SUBTYPE
left join [dbo].[REF_table] t3 on t3.[Work Type] = A.WORKTYPE
where ACT_FINISH between '2018-04-30' and '2018-06-01' and LOCATION = 'ABC' AND WORKTYPE = 'C'
and field_remarks is not null
and t3.[Work Type Description] is not null
GROUP BY
      a.WONUM
     ,A.LOCATION
     ,a.WORKTYPE
     ,A.WORK_SUBTYPE
     ,A.JOB_TYPE
     ,t.[Budget Process Description]
     ,t2.[Sub Type Description]
     ,t3.[Work Type Description]
     ,PROJECT_NUMBER
   order by
        PROCESS_NAME