How do I compute for the total of the value in Alias vladd1, vladd2, vladd3, vladd4 or any alternative using declare statement ?

Question

How do I compute for the total of the value in Alias vladd1, vladd2, vladd3, vladd4 or any alternative using declare statement ?

arnold-491793

Say Hey Kid

Points: 709
More actions
November 22, 2009 at 11:55 pm

#212800

Hi,
How do I compute for the total of the value in Alias vladd1, vladd2, vladd3, vladd4 or any alternative using declare statement ?
Here is my sample statement below.
select alan8,
case
when rtrim(aladd1) <> '' then len(aladd1) + 9 else 0 end as vladd1 ,
case
when rtrim(aladd1) <> '' then len(aladd2) + 9 else 0 end as vladd2 ,
case
when rtrim(aladd1) <> '' then len(aladd3) + 9 else 0 end as vladd3 ,
case
when rtrim(aladd1) <> '' then len(aladd4) + 9 else 0 end as vladd4,
vladd1 + vladd2 + vladd3 + vladd4 as Total
from Customers
Thanks and appreciate your help.
Regards,
cyberarnet

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

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

arnold-491793 (11/22/2009)
select alan8,
case
when rtrim(aladd1) <> '' then len(aladd1) + 9 else 0 end as vladd1 ,
case
when rtrim(aladd1) <> '' then len(aladd2) + 9 else 0 end as vladd2 ,
case
when rtrim(aladd1) <> '' then len(aladd3) + 9 else 0 end as vladd3 ,
case
when rtrim(aladd1) <> '' then len(aladd4) + 9 else 0 end as vladd4,
vladd1 + vladd2 + vladd3 + vladd4 as Total
from Customers

Two ways that I can think of are,

Select vladd1, vladd2, vladd3, vladd4 , (vladd1+ vladd2 + vladd3 + vladd4) FROM

(select alan8,

case

when rtrim(aladd1) <> '' then len(aladd1) + 9 else 0 end as vladd1 ,

case

when rtrim(aladd1) <> '' then len(aladd2) + 9 else 0 end as vladd2 ,

case

when rtrim(aladd1) <> '' then len(aladd3) + 9 else 0 end as vladd3 ,

case

when rtrim(aladd1) <> '' then len(aladd4) + 9 else 0 end as vladd4,

vladd1 + vladd2 + vladd3 + vladd4 as Total

from Customers) t

and

(select alan8,

case

when rtrim(aladd1) <> '' then len(aladd1) + 9 else 0 end as vladd1 ,

case

when rtrim(aladd1) <> '' then len(aladd2) + 9 else 0 end as vladd2 ,

case

when rtrim(aladd1) <> '' then len(aladd3) + 9 else 0 end as vladd3 ,

case

when rtrim(aladd1) <> '' then len(aladd4) + 9 else 0 end as vladd4,

<<repeating and adding all your case statements here!>>

from Customers)

Still not sure if case is on the same column aladd1 for all thos columns as you are checking the same condition!

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

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 2

This is cool. Thanks. That's right the 3 aladd1 in case statement should be aladd2, aladd3, aladd4. Just a typo error.

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 3

Heh... I just can't resist...

Since all of the following...

SELECT LEN(' '),

LEN(' '),

LEN('')

... equal zero, there is no need for the CASE statements nor the RTRIM comparisons. The code below is only a second faster on a million rows (about 4.9 CPU seconds instead of 5.9 CPU seconds... still, that's a 17% improvement) and probably isn't worth the obfuscation but forget all of that... it's just fun to write something totally different once in a while...

WITH

ctePreAgg AS

(

SELECT alan8,

ISNULL(NULLIF(LEN(aladd1),0)+9,0) AS vladd1,

ISNULL(NULLIF(LEN(aladd2),0)+9,0) AS vladd2,

ISNULL(NULLIF(LEN(aladd3),0)+9,0) AS vladd3,

ISNULL(NULLIF(LEN(aladd4),0)+9,0) AS vladd4

FROM dbo.Customers

)

SELECT alan8, vladd1, vladd2, vladd3, vladd4,

vladd1 + vladd2 + vladd3 + vladd4 AS Total

FROM ctePreAgg;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)