Lowest Value between 3 columns

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

Lowest Value between 3 columns

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2012-09-14 : 02:23:18

Hi

I have this code below and I need calculate the last Amount, which has to be the lowest value between [Original_Investment_Value] and (Encashment_Value + [Top-Up_Commission]), I need to return a lowest value between those values

Please help

SELECT

@AppId AS Application_Id
, MB.POLICY_ID AS Policy_Id
, CAST(ISNULL(MH.BFN_CHG_DATE, '')AS VARCHAR) AS Date_Of_Disinvestment
,CASE
WHEN
(@x = 'Top Ups' and @y <> 'Transfer in ISA') OR (@x = 'Top Ups - Retirement' and @y not like '%Transfer%')
THEN (select a.X_INVEST_AMT
from S_OPTY a
left join S_SALES_METHOD b
on a.X_SALES_METHOD_ID = b.ROW_ID
where a.NAME = @AppId)

END[Original_Investment_Value]

,ISNULL(MB.TOTAL_AMOUNT, 0.00) AS Encashment_Value
,SUM(
CASE
WHEN SP.IFA_REMUNE_TYPE = 'COMMISSION'
THEN WH.TOT_COMMISSION
END)
END [Top-Up_Commission]

Now I need calculate the last Amount, which has to be the lowest value between [Original_Investment_Value] and (Encashment_Value + [Top-Up_Commission]), I need to return a lowest value between those values

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-14 : 03:14:11

[code]
select
...,
case when [Original_Investment_Value] < (Encashment_Value + [Top-Up_Commission])
then [Original_Investment_Value]
else Encashment_Value + [Top-Up_Commission]
end as YourColumnName,
...
from ...
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:15:57

make sure below code returns only row


select a.X_INVEST_AMT
from S_OPTY a
left join S_SALES_METHOD b
on a.X_SALES_METHOD_ID = b.ROW_ID
where a.NAME = @AppId

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources