March 18, 2008 at 3:25 pm
I'm thinking that the GROUP BY is irrelevant and that what is needed inplace of the CASE WHEN SUM(v.PurchaseAmt)/15000 < 0 ... is this
FLOOR(v.PurchaseAmt/15000)
😎
March 18, 2008 at 3:26 pm
That too....
March 18, 2008 at 3:27 pm
Is this not a requirement anymore? 0.0243 would fall into this category.
Exactly. If the Purchase amount is less than a whole number then I want to see a 0.
March 18, 2008 at 3:28 pm
Well I take it as between 0 and 1. Is that what you meant?
March 18, 2008 at 3:28 pm
when numerics are divided, the result is a different sized numeric and it typically has a larger scale than the original value. so numeric(19,2)/int will result in a numeric(25,8) (for example). 0.000123 is indeed > 0 so that's what's going to come out of your case. instead of comparing to 0, you'd need to compare to the least significant value for your usage (such as 0.01).
do you have negative purchaseAmounts? if not, then all you need do is sum the rounded result:
sum( round(v.PurchaseAmount / 15000,2) ) as 'TotalPersonalSalesCalculatedPoints'
if you do have negative purchaseAmounts then this should work:
sum( round(
case
when v.PurchaseAmount > 0
then v.PurchaseAmount
else 0
end / 15000,2) ) as 'TotalPersonalSalesCalculatedPoints'
lastly, if possible consider using the money datatype. it stores up to +/-922,337,203,685,477.5807 and after division the result is still money.
March 18, 2008 at 3:38 pm
Revisiting the spec it is any thing less than 1 (whole Number) that they want to see a 0.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 18, 2008 at 3:50 pm
Then use the code I posted earlier.
CASE
WHEN SUM(v.PurchaseAmount) /15000 <=0 THEN 0
WHEN SUM(v.PurchaseAmount) /15000 > 0 AND SUM(v.PurchaseAmount) /15000 < 1 THEN 0
ELSE SUM(v.PurchaseAmount)/15000
END as 'TotalPersonalSalesCalculatedPoints'
March 18, 2008 at 3:53 pm
Or - if you like simple...
CASE
WHEN SUM(v.PurchaseAmount) /15000 < 1 THEN 0
ELSE SUM(v.PurchaseAmount)/15000
END as 'TotalPersonalSalesCalculatedPoints'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 3:57 pm
From the tests I had the OP run, the GROUP BY and SUM are not needed.
I believe he has the solution.
😎
March 18, 2008 at 3:58 pm
Simple is best I used this route:
,CASE
WHEN v.PurchaseAmount/15000 <=1 THEN 0
ELSE v.PurchaseAmount/15000
END as 'TotalPersonalSalesCalculatedPoints'
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 18, 2008 at 4:00 pm
Yep, that works too.
March 18, 2008 at 6:07 pm
alorenzini (3/18/2008)
Simple is best I used this route:,CASE
WHEN v.PurchaseAmount/15000 <=1 THEN 0
ELSE v.PurchaseAmount/15000
END as 'TotalPersonalSalesCalculatedPoints'
that simple version doesn't have a sum. i don't know your data (still wonder if you have negative purchase amounts), but that route will exclude any entry that's less than 15,000. will a consultant be cheated if they have 10 'purchases' for 10,000 each (100K total) but still wind up with 0 'sales points'? if so, you need to divide the sum(), not the individual purchase amount.
if you're only reporting whole numbers, just round the summation:
round( sum(v.PurchaseAmount)/15000, 0)
if you need to report some decimal positions, then you'll need the case:
case when sum(v.PurchaseAmount)/150000 < 1
then 0 else sum(v.PurchaseAmount) end
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply