SELECT 
	BM.MerchantID,
	M.MerchantName,
	TotalSold,
	TotalTransactions,
	TR.TransTierStart,
	TR.TransTierEnd,
	TR.TransTierRate,
	TD.DiscTierStart,
	TD.DiscTierEnd,
	TD.DiscTierRate,
	RR.ChargebackRate,
	RR.CreditRate
	
FROM
	tblBrokerMerchants BM
		LEFT OUTER JOIN tblMerchants M
			ON BM.MerchantID = M.MerchantID
		LEFT OUTER JOIN 
		(
		SELECT 
			MerchantID,
			SUM(Amount) AS TotalSold,
			COUNT(*) AS TotalTransactions
		FROM
			tblOrders
		WHERE
			OrderDate >= @StartDate
			AND OrderDate <= @EndDate
		GROUP BY
			MerchantID
		) AS tOrders
			ON BM.MerchantID = tOrders.MerchantID
		LEFT OUTER JOIN tblTransactionRates TR
			ON BM.MerchantID = TR.MerchantID
				AND TR.UserTypeID = 2
				AND TR.TransTierStart <= tOrders.TotalTransactions
				AND TR.TransTierEnd >= tOrders.TotalTransactions
		LEFT OUTER JOIN tblDiscountRates TD
			ON BM.MerchantID = TD.MerchantID
				AND TD.UserTypeID = 2
				AND TD.DiscTierStart <= tOrders.TotalSold
				AND TD.DiscTierEnd >= tOrders.TotalSold
		LEFT OUTER JOIN tblReturnRates RR
			ON BM.MerchantID = RR.MerchantID
				AND RR.UserTypeID = 2
		
WHERE
	BM.BrokerID = @BrokerID
ORDER BY
	M.MerchantName

[...]
		LEFT OUTER JOIN tblTransactionRates TR
			ON tOrders.MerchantID = TR.MerchantID
				AND TR.UserTypeID = 2
				AND TR.TransTierStart <= tOrders.TotalTransactions
				AND TR.TransTierEnd >= tOrders.TotalTransactions
		LEFT OUTER JOIN tblDiscountRates TD
			ON tOrders.MerchantID = TD.MerchantID
				AND TD.UserTypeID = 2
				AND TD.DiscTierStart <= tOrders.TotalSold
				AND TD.DiscTierEnd >= tOrders.TotalSold
[...]