SELECT DISTINCT
  CP.ClientID,
  CS.FinNumber,
  CS.GroupName,
  CP.ClientProductID,
  PR.ProductCategory,
  PR.ProductName,
  PR.ServiceCoverIncreasePercentage,
  ST.audPre AS OldStartDate,
  ST.audPost AS NewStartDate,
  CC.NextIncreaseDate,
  fnClientProductRetainer(CP.ClientProductID, CV.audPost) AS Retainer,
  CV.audPre AS OldCoverValue,
  (CV.audPost - CV.audPre) AS Increase,
  CV.audPost AS NewCoverValue
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 (SELECT audKey, audDate, audPre, audPost FROM (
      SELECT A.audKey, A.audDate, A.audPre, A.audPost FROM tbAudit AS A
      WHERE (A.audTable = 'taClientProducts') AND (A.audField = 'CoverStartDate') AND (A.audProcess = 'CoverRenewal') AND
            (DATE(A.audDate) >= @aFromDate) AND (DATE(A.audDate) <= @aToDate)
      UNION
      SELECT B.audKey, B.audDate, B.audPre, B.audPost FROM cms_archive.tbAudit AS B
      WHERE (B.audTable = 'taClientProducts') AND (B.audField = 'CoverStartDate') AND (B.audProcess = 'CoverRenewal') AND
            (DATE(B.audDate) >= @aFromDate) AND (DATE(B.audDate) <= @aToDate)
    ) AS M) AS ST ON (ST.audKey = CP.ClientProductID)
    INNER JOIN (SELECT audKey, audDate, audPre, audPost FROM (
      SELECT A.audKey, A.audDate, A.audPre, A.audPost FROM tbAudit AS A
      WHERE (A.audTable = 'taClientProducts') AND (A.audField = 'CoverValue') AND (A.audProcess = 'CoverRenewal') AND
            (DATE(A.audDate) >= @aFromDate) AND (DATE(A.audDate) <= @aToDate)
      UNION
      SELECT B.audKey, B.audDate, B.audPre, B.audPost FROM cms_archive.tbAudit AS B
      WHERE (B.audTable = 'taClientProducts') AND (B.audField = 'CoverValue') AND (B.audProcess = 'CoverRenewal') AND
            (DATE(B.audDate) >= @aFromDate) AND (DATE(B.audDate) <= @aToDate)
    ) AS M) AS CV ON (CV.audKey = CP.ClientProductID)
    INNER JOIN taClientProductCover AS CC ON ((CC.ClientProductID = CP.ClientProductID) AND (CC.StartDate = ST.audPost))
WHERE
  (TRIM(CV.audPre) <> '') AND (TRIM(CV.audPost) <> '') AND
  (TRIM(ST.audPre) <> '') AND (TRIM(ST.audPost) <> '')
ORDER BY
  CP.ClientID,
  CP.ClientProductID