I trying to understand what the .01 in Scotts solution refers to, does any one know why in the CASE statement, inside the accepted solution he uses this "THEN .01"
I'll paste the exact code, in for quicker reference than the link
--create sample/test dataDROP TABLE commissionsCREATE TABLE commissions ( tier TINYINT, flag TINYINT, lowValue DECIMAL(12, 2), highValue DECIMAL(12, 2), pct DECIMAL(3, 2) )INSERT INTO commissions VALUES(0, 0, 0, 2147483647, 0.50) --default commissionINSERT INTO commissions VALUES(1, 1, 0, 100000, 0.50)INSERT INTO commissions VALUES(2, 1, 100000.01, 250000, 0.55)INSERT INTO commissions VALUES(3, 1, 250000.01, 2147483647, 0.60)DROP TABLE revenuesCREATE TABLE revenues ( empNum INT, gross DECIMAL(12, 2), commissionFlag TINYINT )INSERT INTO revenues VALUES(1111111, 300500, 1)INSERT INTO revenues VALUES(2222222, 300500, 0)SELECT rev.empNum, rev.gross, SUM(CASE WHEN rev.gross > com.highValue THEN com.highValue - com.lowValue + CASE WHEN com.lowValue <> 0 THEN .01 ELSE 0 END ELSE rev.gross - com.lowValue + CASE WHEN com.lowValue <> 0 THEN .01 ELSE 0 END END * com.pct) AS [commission]FROM revenues revINNER JOIN commissions com ON rev.commissionFlag = com.flag AND rev.gross >= com.lowValueGROUP BY rev.empNum, rev.gross
Thanks Paul, I get it now, appreciate the fast response.
PS - I'm posting a related, but different question in a few minutes, hopefully you'll again be able to help, thank you
I.e.
Sum (.01)
Results in the total percentage to apply
Quite crafty actually.