SELECT
  B.*,
  IF(B.GoldilocksFactor = 0, 0.0, IF(B.TotalServiceValue < 0, 0.0,
    (@nGrossProfitMargin - B.GoldilocksFactor / 100.0 * @nGrossProfitMargin) * (100.0 - @nGrossProfitMargin) / @nGrossProfitMargin)) AS LossOfGrossMargin
FROM (

SELECT
  A.LabourNetBranchID,
  A.LabourNetBranchName,
  A.ProductCategory,
  GROUP_CONCAT(DISTINCT A.Batch) AS Batch,
  SUM(A.CurrentRetainerFee) AS CurrentRetainerFee,
  SUM(A.ServiceValue12) AS ServiceValue12,
  SUM(A.ServiceValue11) AS ServiceValue11,
  SUM(A.ServiceValue10) AS ServiceValue10,
  SUM(A.ServiceValue9) AS ServiceValue9,
  SUM(A.ServiceValue8) AS ServiceValue8,
  SUM(A.ServiceValue7) AS ServiceValue7,
  SUM(A.ServiceValue6) AS ServiceValue6,
  SUM(A.ServiceValue5) AS ServiceValue5,
  SUM(A.ServiceValue4) AS ServiceValue4,
  SUM(A.ServiceValue3) AS ServiceValue3,
  SUM(A.ServiceValue2) AS ServiceValue2,
  SUM(A.ServiceValue1) AS ServiceValue1,
  COALESCE((SUM(A.TotalServiceValue) / SUM(A.AnnualRetainerFee)) * 100.0, 0.0) AS GoldilocksFactor,
  SUM(A.AnnualRetainerFee) AS AnnualRetainerFee,
  SUM(A.TotalServiceValue) AS TotalServiceValue
FROM (

@pInnerSQL

) AS A
LEFT JOIN (

  SELECT ClientID, fnClientLabourNetRegionCount(ClientID) AS RegionCount FROM taClients

) AS X USING (ClientID)
WHERE
  ((@nNationalStatus < 0) OR
   ((@nNationalStatus = 0) AND (IF(X.RegionCount > 1, 'National', 'Local') = 'Local')) OR
   ((@nNationalStatus > 0) AND (IF(X.RegionCount > 1, 'National', 'Local') = 'National')))
  @pConsultantID
GROUP BY 1,2,3

) AS B
WHERE
  IF(@nGoldilocksRange NOT IN (1,2,3),
    TRUE,
    IF(@nGoldilocksRange = 1,
      IF((B.AnnualRetainerFee <= 0) OR ((COALESCE(B.TotalServiceValue / B.AnnualRetainerFee, 0.0) * 100) > @nGoldilocksHigh), TRUE, FALSE),
      IF(@nGoldilocksRange = 2,
        IF((B.AnnualRetainerFee > 0) AND ((COALESCE(B.TotalServiceValue / B.AnnualRetainerFee, 0.0) * 100) < @nGoldilocksLow), TRUE, FALSE),
        IF(@nGoldilocksRange = 3,
          IF((B.AnnualRetainerFee > 0) AND ((COALESCE(B.TotalServiceValue / B.AnnualRetainerFee, 0.0) * 100) BETWEEN @nGoldilocksLow AND @nGoldilocksHigh), TRUE, FALSE),
          FALSE
        )
      )
    )
  ) AND
  ((B.TotalServiceValue > 0.0) OR (B.AnnualRetainerFee > 0.0))

ORDER BY
  B.LabourNetBranchName,
  B.ProductCategory
