Complecated Sql query

• kaushal_shah26

  Mr or Mrs. 500

  Points: 516

  More actions

  May 4, 2012 at 12:39 pm

  #261038

  Hello Guys, I am writing a complecated sql query and I would really appreciate if you can help me to figure out.

  The logic is below but that needs to be part of Select statement.

  If (FinancialStatus = "Customer")

  then Amount = 0

  else If (Status = "Hold")

  then MinPrice = Mintransferprice

  else If (OriginalCost > 0)

  then OCost = (originalCost * MinPricePercentage /100)

  else If (BeforeActivationPrice > 0 )

  then BCost = (BeforeActivationPrice * MinPricePercentage /100)

  Upto this point is easy. I can use Case Output when xxx then yyy when aaa then bbbb but my problem is

  I need to find Maximum value of Amount, MinPrice, OCost and BCost

  -------------------

  This all needs to be done in a single select statement.

  Thanks in advance for your help.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  May 4, 2012 at 12:51 pm

  #1483728

  Doable, but with a bit of help from you.

  Please read the following article and provide us create table, sample data and ur expected output based off of the sample data

  How to post questions[/url]

• imex

  Ten Centuries

  Points: 1234

  More actions

  May 4, 2012 at 2:34 pm

  #1483763

  Hi,

  I agree with the ColdCoffee.

  Also I could not understand very well.

  Maybe you can use this:

  MAX( case ... ) as MaxAmount

  Hope this helps.

  https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

• kaushal_shah26

  Mr or Mrs. 500

  Points: 516

  More actions

  May 4, 2012 at 8:01 pm

  #1483789

  I figured out the solution. Thanks for the help.

  case when t.FinancialStatus = ''Customer'' then 0 else

  case when (cfc.OriginalCostBase * ccc.mintransferpricepercentage /100) > (cfc.beforeactivationcostusd * ccc.mintransferpricepercentage /100) and

  (cfc.OriginalCostBase * ccc.mintransferpricepercentage /100) > (ccc.mintransferpriceusd)

  then (cfc.OriginalCostBase * ccc.mintransferpricepercentage /100)

  else

  case when (cfc.beforeactivationcostusd * ccc.mintransferpricepercentage /100) > (ccc.mintransferpriceusd)

  then (cfc.beforeactivationcostusd * ccc.mintransferpricepercentage /100)

  else

  (ccc.mintransferpriceusd)

  end

  end

  End as [MinTransferPrice]