Add multiple columns in select query

Question

Scenario is like:

SomeTable { A int, B int, C int }

I need to select and add with formula: A + 25%B + 50%C. If in decimal, round upto next full digit and display an asterisk and a spcae preciding the sum. Each column needs to be rounded off before adding.

Suppose data is

10 16 12
14 15 19

Select should return: 20, 28 *.

I am thinking of using case statements but its getting very dirty with that.
Any ideas?

Is there a reason why you are doing this in your database instead of extracting the values and then performing the mathematical operations on them in the application that is using the data? — Thomas Owens
– Thomas Owens, Commented May 6, 2010 at 11:38
I am using this select in a stored procedure. The SP is linked to SSRS report. Point is that if there are any minor changes the reports need not be deployed again. A simple change in stored proc would get the job done. — A G
– A G, Commented May 6, 2010 at 12:01

KM. · Accepted Answer · 2010-05-06 11:50:38Z

1

CASE is your only way:

DECLARE @YourTable table (A int, B int, C int)
SET NOCOUNT ON
INSERT @YourTable VALUES (10, 16, 12 )
INSERT @YourTable VALUES (14, 15, 19 )
SET NOCOUNT OFF

SELECT 
    CASE
        WHEN ROUND(A+.25*B+C*.5,0)<A+.25*B+C*.5 THEN
            CONVERT(varchar(10),CONVERT(int,ROUND(A+.25*B+C*.5,0))+1)+' *' 
        ELSE CONVERT(varchar(10),CONVERT(int,ROUND(A+.25*B+C*.5,0))) 
    END
    FROM @YourTable

OUTPUT:

------------
20
28 *

(2 row(s) affected)

answered May 6, 2010 at 11:50

KM.

104k34 gold badges183 silver badges214 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add multiple columns in select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related