How to minimize my big query for showing counts using SQL

I am taking counts from CTE but common is Status(working,pening) and Divisions but my query is becoming big because left join is same but just status and division are different. I have wrote 10 left join count but by passing status and divisions.

Below is my whole sql query

declare @createdBy int=79

;with cte as (                
select max(w.WorkingNo)WorkingNo 
from                
working w 
join workingdealhistory wd on wd.WorkHistoryId=w.workingNo and 
w.status  IN ('WORKING','PENDING')     and w.mhlId>0  and w.IsActive=1
join TreasureTrove t on t.CandidateId=w.CandidateId and t.DepartmentId=2
group by w.CandidateId,w.status                
)   

select distinct m.potentialHospitalNo 
,m.hospital                       
,ph.clientname
,cdiwr.working cdiworking
,cdipn.pending cdipending
,himwr.working himworking
,himpn.pending himpending
,cmurwr.working cmurworking
,cmurpn.pending cmurpending                 
,odmwr.working odmworking
,odmpn.pending odmpending                   
,traumawr.working traumaworking
,traumapn.pending traumapending
,ph.ClientId
from PotentialHospitlMaster m (NOLOCK)
Inner JOIN HospitalStatus HS (NOLOCK) On m.potentialHospitalNo=HS.ClientId
inner join potentialhospital ph on ph.potentialhospitalno=m.potentialhospitalno
          
    --this is status='working' and division='CDI'         
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='CDI' group by w.MHLId) as cdiwr on cdiwr.MHLId=ph.potentialHospitalNo 

    --this is status='pending' and division='CDI'   
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='CDI' group by w.MHLId) as cdipn on cdipn.MHLId=ph.potentialHospitalNo 

    --this is status='working' and division='HIM'
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='HIM' group by w.MHLId) as himwr on himwr.MHLId=ph.potentialHospitalNo 

  --this is status='pending' and division='HIM'
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='HIM' group by w.MHLId) as himpn on himpn.MHLId=ph.potentialHospitalNo                     

--this is status='working' and division='CMUR'
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='CMUR' group by w.MHLId) as cmurwr on cmurwr.MHLId=ph.potentialHospitalNo 

--this is status='pending' and division='CMUR'
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='CMUR' group by w.MHLId) as cmurpn on cmurpn.MHLId=ph.potentialHospitalNo                      

--this is status='working' and division='ODM'
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='ODM' group by w.MHLId) as odmwr on odmwr.MHLId=ph.potentialHospitalNo 

--this is status='pending' and division='ODM'
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='ODM' group by w.MHLId) as odmpn on odmpn.MHLId=ph.potentialHospitalNo                         

--this is status='working' and division='Trauma'
LEFT join(select COUNT(w.WorkingNo)working, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='working' and wh.Division='Trauma' group by w.MHLId) as traumawr on traumawr.MHLId=ph.potentialHospitalNo 

--this is status='pending' and division='Trauma'
LEFT join(select COUNT(w.WorkingNo)pending, MHLId from cte c inner join
working w on w.WorkingNo=c.WorkingNo 
inner join WorkingDealHistory WH on w.WorkingNo=WH.WorkHistoryId                         
inner join potentialhospital phs on phs.potentialHospitalNo=w.MHLId where w.Status='pending' and wh.Division='Trauma' group by w.MHLId) as traumapn on traumapn.MHLId=ph.potentialHospitalNo 

where  m.IsActive=1 and HS.UpdatedStatus='MSA Sent' and HS.CreatedBy=@createdBy 
                    

Is there anything which we can minimize query by using group by or anything.

Answer

I’ve taken a bit of a stab at your schema, and I think you could do it with something like:

DECLARE @createdBy INT = 79;

WITH cte AS
(                
    SELECT  w.CandidateId, w.status, WorkingNo = MAX(w.WorkingNo)
    FROM    working AS w
            JOIN workingdealhistory AS wd
                ON  wd.WorkHistoryId = w.workingNo
                AND w.status IN ('WORKING', 'PENDING')
                AND w.mhlId > 0
                AND w.IsActive = 1
            JOIN TreasureTrove AS t
                ON  t.CandidateId = w.CandidateId
                AND t.DepartmentId = 2
    GROUP BY
            w.CandidateId, w.status;              
)   
SELECT  DISTINCT
        m.potentialHospitalNo,
        m.hospital,
        ph.clientname,
        cnt.CDIworking,
        cnt.CDIPending,
        cnt.HIMworking,
        cnt.HIMPending,
        cnt.CMURworking,
        cnt.CMURPending,
        cnt.ODMworking,
        cnt.ODMPending,
        cnt.Traumaworking,
        cnt.TraumaPending
        ph.ClientId
FROM    PotentialHospitlMaster AS m
        INNER JOIN HospitalStatus AS HS
            ON m.potentialHospitalNo = HS.ClientId
        INNER JOIN potentialhospital AS ph
            ON ph.potentialhospitalno = m.potentialhospitalno   
        LEFT JOIN
        (
            SELECT  w.MHLId,
                    CDIworking = COUNT(CASE WHEN w.Status = 'working' AND wh.Division = 'CDI' THEN w.WorkingNo END),
                    CDIPending = COUNT(CASE WHEN w.Status = 'pending' AND wh.Division = 'CDI' THEN w.WorkingNo END),
                    HIMworking = COUNT(CASE WHEN w.Status = 'working' AND wh.Division = 'HIM' THEN w.WorkingNo END),
                    HIMPending = COUNT(CASE WHEN w.Status = 'pending' AND wh.Division = 'HIM' THEN w.WorkingNo END),
                    CMURworking = COUNT(CASE WHEN w.Status = 'working' AND wh.Division = 'CMUR' THEN w.WorkingNo END),
                    CMURPending = COUNT(CASE WHEN w.Status = 'pending' AND wh.Division = 'CMUR' THEN w.WorkingNo END),
                    ODMworking = COUNT(CASE WHEN w.Status = 'working' AND wh.Division = 'ODM' THEN w.WorkingNo END),
                    ODMPending = COUNT(CASE WHEN w.Status = 'pending' AND wh.Division = 'ODM' THEN w.WorkingNo END),
                    Traumaworking = COUNT(CASE WHEN w.Status = 'working' AND wh.Division = 'Trauma' THEN w.WorkingNo END),
                    TraumaPending = COUNT(CASE WHEN w.Status = 'pending' AND wh.Division = 'Trauma' THEN w.WorkingNo END)
            FROM    working AS w
                    INNER JOIN WorkingDealHistory AS wh
                        ON w.WorkingNo = wh.WorkHistoryId
                    INNER JOIN cte AS c
                        ON c.CandidateId = w.CandidateId
                        AND c.status = w.status
                        AND c.WorkingNo = w.WorkingNo
            GROUP BY
                    w.MHLId
        ) AS cnt
            ON cnt.MHLId = ph.potentialHospitalNo
WHERE   m.IsActive = 1
AND     HS.UpdatedStatus = 'MSA Sent'
AND     HS.CreatedBy = @createdBy;

N.B. I’ve removed NOLOCK as putting this everywhere is a bad habit to kick. Also, I am deeply suspicious of most queries that use DISTINCT across a large number of columns like this. More often than not the duplicates are a symptom of an error with the query, and DISTINCT is just a nasty plaster trying to cover up the real issue. You should work out where duplicates are coming from and look to remove either early or at least in a deterministic fashion.