I have a fairly simple CASE statement.
In this query I derive at my commission value based on calculated values from tblCommissionTiers .
The query works well for the tiers below 100%. I'm now trying to use a variable @MaxComm to pass the highest CommissionPercentage to the same calculation as the lower values for instances when Target is exceeded.
in other words, whenever gross is more than targ.SalesTarget, I would like to dynamically apply the highest CommissionPercentage.
Can anyone assist me with using paramtres inside a CASE statement please?
here is my script
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
My concern is the zero output in the COMMISSION column
here are my output values