DECLARE @MaxComm decimal (3,2) SET @MaxComm = ( select max(CommissionPercentage) from tblCommissionTiers) SELECT DISTINCT empNum, gross as SALES ,targ.SalesTarget AS TARGET , lowPercentage,highPercentage, CommissionPercentage ,gross*lowPercentage AS LowAmount ,gross*highPercentage AS HighAmount ,(gross*highPercentage) - (gross*lowPercentage) VarHighVsLow ,CASE WHEN gross > targ.SalesTarget THEN ((gross*highPercentage) - (gross*lowPercentage)) * CommissionPercentage WHEN (gross - targ.SalesTarget) > 100.00 THEN (gross - targ.SalesTarget) * @MaxComm ELSE 0 END COMMISSION FROM tblRevenues rev INNER JOIN tblCommissionTiers com ON rev.commissionFlag = com.commissionFlag INNER JOIN [dbo].[tblTargets] Targ ON com.commissionFlag = targ.commissionFlag GROUP BY empNum, gross,targ.SalesTarget , lowPercentage ,highPercentage, CommissionPercentage
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.