SELECT
  *
FROM
  taClientProductFees AS PF
WHERE
  (PF.ClientBranchProductID = @nClientBranchProductID) AND
  (PF.ClientProductFeeID = (

    SELECT MAX(M.ClientProductFeeID)
    FROM taClientProductFees AS M
    WHERE
      (M.ClientBranchProductID = @nClientBranchProductID) AND
      (M.StartDate = (

        SELECT MAX(N.StartDate)
        FROM taClientProductFees AS N
        WHERE
          (N.ClientBranchProductID = @nClientBranchProductID) AND
          (N.StartDate <= @aEffectiveDate)
      ))
  ))
