Lowest Value between two columns

Question

Lowest Value between two columns

hoseam

SSCertifiable

Points: 5533
More actions
September 14, 2012 at 12:26 am

#281537

Hi.
SELECT
@AppId AS Application_Id
, MB.POLICY_ID AS Policy_Id
, MH.BFN_CHG_DATEAS Date_Of_Disinvestment
, AD.X_INVEST_AMT AS Original_Investment_Value
, MB.TOTAL_AMOUNT AS Encashment_Value
, WH.TOT_COMMISSION AS Top-Up_Commission
FROM: I select from lot of tables, as specified from my select statement
My output:
AppId PolicyId Date_Of_DisIn Original_Inv Encashment_Value Top-Up_Commision ???
1-1-1; 1000000; Jan-01-12; 2882.07; 3312.00; 2306; ??
My Aliases returns just one values, of which that's what I am expecting, so I need to make a comparison between Original_Investment_Value and (Encashment_Value + Top-Up_Commision), and return the lowest value
Please help
September 14, 2012 at 1:47 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

hoseam (9/14/2012)
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

Please post table DDL and sample data.Also the full and correct query you tried.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

hoseam SSCertifiable Points: 5533 More actions · Answer 2

Can I clarify my problem

My Aliases returns just one values, of which that's what I am expecting, so I need to take just those values and compare them and return the last column, of which is the lowest value from my comparison.

My output:

AppId PolicyId Date_Of_DisIn Original_Inv Encashment_Value Top-Up_Commision ???

1-1-1; 1000000; Jan-01-12; 2882.07; 3312.00; 2306; ??

Now my last row has to be comparison between Original_Inv and (Encash_Value + Top-Up_Commision), and return the lowest value