Divide two columns with null values

• vigneshkumart50

  Ten Centuries

  Points: 1205

  More actions

  August 14, 2014 at 1:40 am

  #298100

  Hi,

  I am trying to divide these two columns and need

  suggestions.

  parsename('$'+ Convert(varchar,Convert(money,Q.[Product Cost]-R.[Shared Cost]),1),2) as [Pre Cost],

  parsename('$'+ Convert(varchar,Convert(money,R.[New Product Cost]-R.[Shared Cost]),1),2) as [Post Cost]

  Formula:

  [Post Cost] / [Pre Cost]

  Expected:

  If [Post Cost] is null then I would need to assign 1 and divide by [Pre Cost] and similarly if [Pre Cost] is null then I would need to assign 1 so that I can divide [Post Cost] / [Pre Cost]

  Really appreciate any help on this.

• Koen Verbeeck

  SSC Guru

  Points: 259197

  More actions

  August 14, 2014 at 1:54 am

  #1737932

  CASEWHEN [Post Cost] IS NULL OR [Pre Cost] IS NULL

  THEN 1

  WHEN [Pre Cost] = 0 -- avoid divide by zero error

  THEN 0

  ELSE [Post Cost] / [Pre Cost]

  END

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP