Solved

using a variable within a CASE statement

Posted on 2016-09-06
12
35 Views
Last Modified: 2016-09-11
Hi there,
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

Open in new window


My concern is the zero output in the COMMISSION column

here are my output values
my SQL output
0
Comment
Question by:fadiel ras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 2
  • 2
12 Comments
 
LVL 4

Expert Comment

by:Steven Kribbe
ID: 41785915
I think the problem with the zero line is the fact that the first WHEN clause is true, calculating the high - low which is zero. I would expect the calculation to be this:

CASE WHEN gross <= targ.SalesTarget
                  THEN ((gross*highPercentage) - (gross*lowPercentage)) * CommissionPercentage
       WHEN (gross > targ.SalesTarget)
                  THEN (gross - targ.SalesTarget) * @MaxComm
            ELSE 0   END COMMISSION

Or do you want to calculate seperate commission for reaching the target AND max commission for only the part that was above target?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41785924
I rather see that SalesTarget is an integer, which will result the entire result to be computed as integer..

please try this:
replace:
THEN (gross - targ.SalesTarget) * @MaxComm
by this:
THEN ( gross - cast ( targ.SalesTarget as decimal(10,4))  ) * @MaxComm
0
 

Author Comment

by:fadiel ras
ID: 41785925
Thanks Steven. I'll try your CASE asap.
>>Or do you want to calculate separate commission for reaching the target AND max commission for only the part that was above target?
I'm trying to use the variance between the Sales and Target as the base, to then apply the Max commission percentage to that variance...hope that makes sense?
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

Author Comment

by:fadiel ras
ID: 41785929
Thanks Guy. I tried the CAST option, it still returned zero's. let me give Steven's a try and advise.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 41785934
actually, I see that:
WHEN (gross - targ.SalesTarget) > 100.00

for gross = 105, and Target = 100, this will not match, hence it goes to the "else"
hence, Steve is right with  his suggestion
0
 

Author Comment

by:fadiel ras
ID: 41785950
Hi Steven, it's also not that.
I cant change the first condition to less than Target, as their are different tiers ( you can view from the embedded image on my original post)
so I'm hoping to achieve something like an ELSE- if I could simply use the MAX() in the ELSE then that would work perfectly. So I'm thinking to apply the CASE in my calc, then somehow say, everything ELSE may be at the MAX() percentage.
any ideas how to achieve this?
0
 
LVL 4

Assisted Solution

by:Steven Kribbe
Steven Kribbe earned 250 total points
ID: 41785982
maybe a nice approach would be defining a different column in the query for each tier of commission and one column that adds them all up. I still think you can make a different calculation in case of reaching the target or performing better than the target.
I am still a bit confused about the variation bit.
0
 

Author Comment

by:fadiel ras
ID: 41785996
that's exactly what I've just started doing, as I need to understand that I'm doing it correctly (for my own sanity)
if you can help think of another way, then I'd be grateful, it would be much cleaner than the individual columns.

I've inserted an image that might explain my intent better

thanks thus far!
Commission Calc
0
 

Author Comment

by:fadiel ras
ID: 41786007
my apologies everyone. I picked an error in my code! I'll post it after this, then close. very sorry.
0
 

Accepted Solution

by:
fadiel ras earned 0 total points
ID: 41786023
I was passing incorrect logic to my query
The calc should take the gross (sales) less SalesTarget (refer to ROW NR 3 and 13 respectively)
then multiply that with the @MaxComm.
again, my apologies, but thanks for leading to trace my error.

--INCORRECT QUERY
CASE	
  WHEN gross <= targ.SalesTarget
   THEN (gross-targ.SalesTarget) * CommissionPercentage
 WHEN (gross > targ.SalesTarget)
   THEN (gross - targ.SalesTarget) * @MaxComm
    ELSE 0   END COMMISSION


--CORRECT QUERY
CASE 
 WHEN gross <= targ.SalesTarget
  THEN ((gross*highPercentage) - (gross*lowPercentage)) * CommissionPercentage
 WHEN (gross > targ.SalesTarget)
  THEN (gross - targ.SalesTarget) * @MaxComm
   ELSE 0   END COMMISSION

Open in new window

0
 

Author Comment

by:fadiel ras
ID: 41786024
Thanks all
0
 

Author Closing Comment

by:fadiel ras
ID: 41793236
error made with my original post. corrected in the final post.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question