SELECT
  CP.ClientID,
  CS.FinNumber,
  CS.GroupName,
  PR.ProductCategory,
  PR.ProductName,
  CC.StartDate,
  IF((CC.NextIncreaseDate IS NULL) OR (CC.NextIncreaseDate = '0000-00-00'), '-', CC.NextIncreaseDate) AS NextIncreaseDate,
  PR.ServiceCoverIncreasePercentage,
  IF(CC.CoverValue > 0.0,
    IF(CC.ValueResetMonths <= 0, CONCAT('R ', FORMAT(CC.CoverValue, 2), ' once off'),
      CONCAT('R ', FORMAT(CC.CoverValue, 2), ' every', IF(CC.ValueResetMonths = 1, '', CONCAT(' ', FORMAT(CC.ValueResetMonths, 0))), ' month', IF(CC.ValueResetMonths > 1, 's', ''))),
    IF(CC.CoverValue < 0.0, 'Unlimited Cover Value', 'none')
  ) AS CoverValue,
  IF(CC.FreeHours > 0.0,
    IF(CC.HoursResetMonths <= 0, CONCAT(FORMAT(CC.FreeHours, 0), ' hour', IF(CC.FreeHours > 1, 's', ''), ' once off'),
      CONCAT(FORMAT(CC.FreeHours, 0), ' hour', IF(CC.FreeHours > 1, 's', ''), ' every', IF(CC.HoursResetMonths = 1, '', CONCAT(' ', FORMAT(CC.HoursResetMonths, 0))), ' month', IF(CC.HoursResetMonths > 1, 's', ''))),
    IF(FreeHours < 0.0, 'Unlimited Free Hours', 'none')
  ) AS FreeHours,
  IF(CC.FreeVisits > 0.0,
    IF(CC.VisitsResetMonths <= 0, CONCAT(FORMAT(CC.FreeVisits, 0), ' visit', IF(CC.FreeVisits > 1, 's', ''), ' once off'),
      CONCAT(FORMAT(CC.FreeVisits, 0), ' visit', IF(CC.FreeVisits > 1, 's', ''), ' every', IF(CC.VisitsResetMonths = 1, '', CONCAT(' ', FORMAT(CC.VisitsResetMonths, 0))), ' month', IF(CC.VisitsResetMonths > 1, 's', ''))),
    IF(CC.FreeVisits < 0.0, 'Unlimited Free Visits', 'none')
  ) AS FreeVisits,
  fnClientProductRetainer(CP.ClientProductID, CC.StartDate) AS Retainer
FROM
  taClientProducts AS CP
    INNER JOIN taClients AS CS ON (CS.ClientID = CP.ClientID)
    INNER JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    INNER JOIN taClientProductCover AS CC ON (CC.ClientProductID = CP.ClientProductID)
    INNER JOIN (
      SELECT M.ClientProductID, M.StartDate
      FROM taClientProductCover AS M
      WHERE
        (M.StartDate = (
          SELECT MAX(N.StartDate)
          FROM taClientProductCover AS N
          WHERE
            (N.ClientProductID = M.ClientProductID) AND
            (N.StartDate <= @aEffectiveDate)
        ))
    ) AS X ON ((X.ClientProductID = CC.ClientProductID) AND (X.StartDate = CC.StartDate))
WHERE
  (CS.ClientStatus = 1) AND
  (CP.Status = 1)
  @pProductID
  @pCoverStatus
ORDER BY
  CS.FinNumber, CP.ClientProductID