As to me... too many
COUNT(CASE WHEN ...)
I'd emit data into less number of fields than yours, then to pivot data (or count data), as i shown you here:
How to separate field partdone to text yes or no or null based on position ?[
^].
Improved query might look like:
SELECT CompanyID, InForeCast, COUNT(InForeCast) AS CountInForeCast, InTroudctionForeCast, COUNT(InTroudctionForeCast) AS CountInTroudctionForeCast, InTroudctionNoAPPForeCast, COUNT(InTroudctionNoAPPForeCast) AS CountInTroudctionNoAPPForeCast
FROM(
SELECT F.CompanyID, ISNULL(FI.FamilyLevel, 0)=1 AS InForeCast,
InTroudctionForeCast = CASE
WHEN ISNULL(FI.FamilyLevel,0)= 0 THEN ISDATE(PF.IntroductionDate)
END,
InTroudctionNoAPPForeCast = CASE
WHEN ISNULL(FI.FamilyLevel,0)= 0 AND ISDATE(PF.IntroductionDate)=1 THEN ISNULL(PF.Comment,'') LIKE '%appro%'
END
FROM Parts.FamilyIntroductionDate FI WITH(NoLock)
INNER JOIN parts.Nop_PartsFamily F ON F.PartFamilyID =fi.FamilyID
INNER JOIN parts.Nop_Part P WITH(NoLock) ON p.PartsFamilyID=f.PartFamilyID
LEFT OUTER JOIN parts.PartsForecast PF WITH(NoLock) ON pf.PartID=p.PartID
GROUP BY F.CompanyID
) AS T
GROUP BY CompanyID, InForeCast, InTroudctionForeCast, InTroudctionNoAPPForeCast
Note: i have no access to your data, so above query may need to be improved.