sql query to add coumn's values to other column.

Question

0.00/5 (No votes)

See more:

I have table 'ProductMaster' with columns

ProductCode
ProductName
ChapterCode
Basic
CVD
SAD
Density
LCost
Custom

My Stored procedure is..

SQL

Create PROCEDURE [dbo].[PROC_TransactionReport]
(@ProductName as varchar(50),@INR1 as money,@INR2 as money,@INR3 as money,@Dollarrate as money,
 @INR as money,@CustomINR as money,@Quantity as int,@StorageCost as money,@CLFLCharges as money,@LCINT as money,@HandlingLoss as money)
AS
BEGIN

SELECT ProductName,ChapterCode,(@Dollarrate*@INR) BASIC,
    (@Dollarrate*@CustomINR) CustomINR,
    ((@Dollarrate*@CustomINR) * DBO.DIVIDE(PM.LCost,100)) LCOST
FROM ProductMaster PM WITH(NOLOCK)  where Productname=@ProductName

Now i want to add CustomINR and LCOST and display in another column aftre 'LCost' say 'CustomINR+LCost'...how can i do this

Posted 30-Aug-12 1:23am

DileepkumarReddy

Updated 30-Aug-12 1:31am

Sanjay K. Gupta

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2012-08-30T01:29:00

Solution 1

And issue is?

Try:

SQL

SELECT ProductName,ChapterCode,(@Dollarrate*@INR) BASIC,
    (@Dollarrate*@CustomINR) CustomINR,
    ((@Dollarrate*@CustomINR) * DBO.DIVIDE(PM.LCost,100)) LCOST,
    ((@Dollarrate*@CustomINR) + ((@Dollarrate*@CustomINR) * DBO.DIVIDE(PM.LCost,100)))  AS NewSumCustomINRandLCost
FROM ProductMaster PM WITH(NOLOCK)  where Productname=@ProductName

Just added another select for the field needed!

Posted 30-Aug-12 1:29am

Sandeep Mewara

Comments

DileepkumarReddy 30-Aug-12 7:50am

Now i will make it as..
<pre>
SELECT ProductName,ChapterCode,(@Dollarrate*@INR) BASIC,
(@Dollarrate*@CustomINR) CustomINR,
((@Dollarrate*@CustomINR) * DBO.DIVIDE(PM.LCost,100)) LCOST,
(((@Dollarrate*@CustomINR) + ((@Dollarrate*@CustomINR) * DBO.DIVIDE(PM.LCost,100)))*DBO.DIVIDE(PM.BASIC) AS NewSumCustomINRandLCost
FROM ProductMaster PM WITH(NOLOCK) where Productname=@ProductName</pre>

Now i have to add another column calculating certain percentage (PM.CVD%) of above three columns(CUSTOMINR+LCOST+NewSumCustomINRandLCost)..

so every time do i need to do as you did or is there any simpler way to accomplish this..

Thanks for the reply..

Sandeep Mewara 30-Aug-12 8:25am

If that is so common for you, Another way is to define a column in table itself with a constraint such that it will automatically populate the values as per calculation. later just pick the column you need.

Though this would be against normaization of db, but based on needs, exceptions are allowed.

_Amy 30-Aug-12 7:51am

5'ed for Nice Answer. :)

Sandeep Mewara 30-Aug-12 8:25am

Thanks. :)

Jas m · Answer 2 · 2012-08-30T01:33:00

Solution 2

have a look at this;

SQL

SELECT ProductName,ChapterCode,(@Dollarrate*@INR) BASIC,
    (@Dollarrate*@CustomINR) CustomINR,
    ((@Dollarrate*@CustomINR) * DBO.DIVIDE(PM.LCost,100)) LCOST,

((@Dollarrate*@CustomINR) + ((@Dollarrate*@CustomINR) * DBO.DIVIDE(PM.LCost,100))) as 'CustomINR plus LCost'

FROM ProductMaster PM WITH(NOLOCK)  where Productname=@ProductName

Hope this helps :)

Jas

Posted 30-Aug-12 1:33am

Jas m

Comments

DileepkumarReddy 30-Aug-12 7:51am

Now i will make it as..
SELECT ProductName,ChapterCode,(@Dollarrate*@INR) BASIC,
(@Dollarrate*@CustomINR) CustomINR,
((@Dollarrate*@CustomINR) * DBO.DIVIDE(PM.LCost,100)) LCOST,
(((@Dollarrate*@CustomINR) + ((@Dollarrate*@CustomINR) * DBO.DIVIDE(PM.LCost,100)))*DBO.DIVIDE(PM.BASIC)) AS NewSumCustomINRandLCost
FROM ProductMaster PM WITH(NOLOCK) where Productname=@ProductName

Now i have to add another column calculating certain percentage (PM.CVD%) of above three columns(CUSTOMINR+LCOST+NewSumCustomINRandLCost)..

so every time do i need to do as you did or is there any simpler way to accomplish this..

Thanks for the reply..

Jas m 30-Aug-12 8:01am

its all upto you how you want to go ahead, this way is also possible and you can add you calculation in a user-defined function and the just pass the column value to the function as parameter and it returns a scalar value for each row.

Jas