SELECT
  BP.ClientBranchProductID,
  CP.ClientProductID
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
WHERE
  (BP.Status = 1) AND
  (CB.ClientID = @nClientID) AND
  (CP.ClientID = @nClientID) AND
  (CB.ClientBranchID = @nClientBranchID) AND
  (CP.ProductID = @nProductID) AND
  (CP.ClientProductID = (

    SELECT MAX(M.ClientProductID)
    FROM taClientProducts AS M
    WHERE
      (M.Status = 1) AND
      (M.ClientID = CP.ClientID) AND
      (M.ProductID = CP.ProductID) AND
      (M.JoinDate = (

        SELECT MAX(N.JoinDate)
        FROM taClientProducts AS N
        WHERE
          (N.Status = 1) AND
          (N.ClientID = @nClientID) AND
          (N.ProductID = @nProductID) AND
          (N.JoinDate <= @aAttendanceDate)

      ))

  ))
