CODEWITHSUNDEEP
sqlsql-servert-sql
Brian Battles
Brian Battles

Reputation: 45

Better/faster way to do these field calculations?

Anyone know a better way to do these field calculations in a SQL Server View?

SELECT         
TCQ.Generic, 

TPM.WRITTEN_PRM / ((100 - TPM.[CO-PART]) / 100 * CASE WHEN TPM.Factor IS NULL THEN 1 ELSE TPM.[Factor] END) AS Gross100_WRITTEN_PRM,  

TPM.EARNED_PRM / ((100 - TPM.[CO-PART]) / 100 * CASE WHEN TPM.Factor IS NULL THEN 1 ELSE TPM.[Factor] END) AS Gross100_EARNED_PRM, 
TC.Generic = GS.Generic

(FROM table table table blah blah...)

Upvotes: 0

Views: 73

Answers (2)

Gudwlk
Gudwlk

Reputation: 1157

You can use COALESCE() or ISNULL() instead of CASE.

SELECT         
  TCQ.Generic,
    TPM.WRITTEN_PRM / ((100 - TPM.[CO-PART]) / 100 * coalesce ( TPM.Factor, 1) ) AS Gross100_WRITTEN_PRM,  
    TPM.EARNED_PRM / ((100 - TPM.[CO-PART]) / 100 * coalesce ( TPM.Factor, 1)) AS Gross100_EARNED_PRM, 
    TC.Generic = GS.Generic

  (FROM table table table blah blah...)

Upvotes: 2

Stu
Stu

Reputation: 32619

You could refactor as something like the following - untested of course

select         
TPM.WRITTEN_PRM / f.v as Gross100_WRITTEN_PRM,  
TPM.EARNED_PRM / f.v as Gross100_EARNED_PRM, 
from
tables
cross apply (values(100 - TPM.[CO-PART]/isnull(TPM.[Factor],1)*100))f(v)

Upvotes: 1

Related Questions