saved4use
asked on
Case statement to sum values - T-SQL
I'm trying to write a case statement that sums up values, but the one below is producing null values. What am I doing wrong? *In a nutshell, (NL * 3%) + (DP * 1%) + (PR) = NR.
sum((case
when METRIC_CD = ''NL'' then
MTD_ACT * 0.03 else null end) +
(case when METRIC_CD = ''DP'' then
MTD_ACT * 0.01 else null end) +
(case when METRIC_CD = ''PR'' then
MTD_ACT * 1 else null end)) as NR
Below is the data as it appears in the table:
METRIC_CD MTD_ACT
NL 1200
DP 500
PR 3150
DP 2250
PR 550
NL 11000
Thank you for your tremendous assistance.
sum((case
when METRIC_CD = ''NL'' then
MTD_ACT * 0.03 else null end) +
(case when METRIC_CD = ''DP'' then
MTD_ACT * 0.01 else null end) +
(case when METRIC_CD = ''PR'' then
MTD_ACT * 1 else null end)) as NR
Below is the data as it appears in the table:
METRIC_CD MTD_ACT
NL 1200
DP 500
PR 3150
DP 2250
PR 550
NL 11000
Thank you for your tremendous assistance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sum((case
when METRIC_CD = ''NL'' then MTD_ACT * 0.03
when METRIC_CD = ''DP'' then MTD_ACT * 0.01
when METRIC_CD = ''PR'' then MTD_ACT * 1
else 0 end)) as NR