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.
CodePudding user response:
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.