Com. Calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Com. Calculation

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2014-05-15 : 05:18:51

Hi All,

I need to find a infected quotes where the commission was paid lower than below condition. 'AgentCommission.CommissionAmount' field has all paid commission fig's

Condition :
Commission should be minimum £3.00
Commission should be min 31% of the totalprice (which ever is greater)

I wrote the below query however it will check only the CommissionAmount is >=31%. I need to modify and include the another rule that commission also be min £3.00 even the paid commission is 31% of totalprice

CASE WHEN CONVERT(decimal(5,2),round((AgentCommission.CommissionAmount ),2))> = CONVERT(decimal(5,2),round((Quote .totalprice )*(0.31),2))
THEN 'NO'ELSE 'YES' END AS Pricediff

The above query brings the below result. As per first condition CommissionAmount is greater than 31% of its total price but its less than £3.00. So i need to show here 'YES' in PriceDiff column.

Quoteid Totalprice CommissionAmount PriceDiff
001 6.02 2.05 No

Any help will be highly appreciated.

Thanks

Regards,
SG

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-15 : 05:36:06

[code]--Condition :
--Commission should be minimum £3.00
--Commission should be min 31% of the totalprice (which ever is greater)

--I wrote the below query however it will check only the CommissionAmount is >=31%. I need to modify and include the another rule that commission also be min £3.00 even the paid commission is 31% of totalprice

--CASE WHEN CONVERT(decimal(5,2),round((AgentCommission.CommissionAmount ),2))> = CONVERT(decimal(5,2),round((Quote .totalprice )*(0.31),2))
-- THEN 'NO'ELSE 'YES' END AS Pricediff

--The above query brings the below result. As per first condition CommissionAmount is greater than 31% of its total price but its less than £3.00. So i need to show here 'YES' in PriceDiff column.

--Quoteid Totalprice CommissionAmount PriceDiff
--001 6.02 2.05 No

declare @ComAmt decimal(5,2) = 2.05
declare @TotPr decimal(5,2) = 6.02
declare @Minimum decimal(5,2) = 3

select
CASE WHEN CONVERT(decimal(5,2),round((@ComAmt ),2))> = CONVERT(decimal(5,2),round((@TotPr )*(0.31),2))
AND CONVERT(decimal(5,2),round((@ComAmt ),2))> = @Minimum
THEN 'NO'ELSE 'YES' END AS Pricediff
[/code]

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources