Adding Column Values and displaying in another column

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi everyone,here is my issue.

I am doing some calculations in stored procedure and display as below ...I want to add Basic,BasicDuty,CVD,Custom,AddDuty and display it as ActualCost

ProductName ProductCode Basic BasicDuty CVD Custom AddDuty ActualCost

Produc1 P1 4553 34 32 143 11 4553+34+32+143+11

How can i do this..help me out

Here's the actual proc

SQL

select ProductName,ChapterCode,ISNULL(@Dollarrate,0)*ISNULL(@INR,0) BASIC,
    ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0) CustomINR,
    (ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)) * DBO.DIVIDE(PM.LCost,100) LCOST,
    (((ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0) * DBO.DIVIDE(PM.LCost,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)))*DBO.DIVIDE(PM.BASIC,100)) BASICDUTY,
     ((((ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0) * DBO.DIVIDE(PM.LCost,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)))*DBO.DIVIDE(PM.BASIC,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)) * DBO.DIVIDE(PM.LCost,100)+ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0))*DBO.DIVIDE(PM.CVD,100) CVD,
     ((((ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0) * DBO.DIVIDE(PM.LCost,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)))*DBO.DIVIDE(PM.BASIC,100))+((((ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0) * DBO.DIVIDE(PM.LCost,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)))*DBO.DIVIDE(PM.BASIC,100))+
     (ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)) * DBO.DIVIDE(PM.LCost,100)+ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0))*DBO.DIVIDE(PM.CVD,100))*DBO.DIVIDE(PM.Custom,100) Custom,
     (ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)) * DBO.DIVIDE(PM.LCost,100)+(((ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0) * DBO.DIVIDE(PM.LCost,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)))*DBO.DIVIDE(PM.BASIC,100))+((((ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0) * DBO.DIVIDE(PM.LCost,100))+
(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)))*DBO.DIVIDE(PM.BASIC,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)) * DBO.DIVIDE(PM.LCost,100)+ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0))*DBO.DIVIDE(PM.CVD,100)+((((ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0) * DBO.DIVIDE(PM.LCost,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)))*DBO.DIVIDE(PM.BASIC,100))+
((((ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0) * DBO.DIVIDE(PM.LCost,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)))*DBO.DIVIDE(PM.BASIC,100))+(ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0)) * DBO.DIVIDE(PM.LCost,100)+ISNULL(@Dollarrate,0)*ISNULL(@CustomINR,0))*DBO.DIVIDE(PM.CVD,100))*DBO.DIVIDE(PM.Custom,100))*DBO.DIVIDE(PM.SAD,100) ADDDUTY
    FROM ProductMaster PM WITH(NOLOCK)  where Productname=@ProductName

Posted 23-Aug-12 2:00am

DileepkumarReddy

Updated 23-Aug-12 2:12am

Christian Graus

v3

Add a Solution

1 solution

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)

Christian Graus · Answer 1 · 2012-08-23T02:02:00

Solution 1

What is your stored proc now ?

Select productname, productcode, basic, basicduty, cvd, custom, addduty, ( productname + productcode + basic + basicduty + cvd + custom + addduty) as ActualCost from TableName

seems the obvious solution

Posted 23-Aug-12 2:02am

Christian Graus

Comments

DileepkumarReddy 23-Aug-12 8:05am

Basic,BasicDuty...are not column names...they are calculating from procedure.

Christian Graus 23-Aug-12 8:09am

Well, in that case, it's impossible to answer your question as it stands, we need to see the proc, but, the answer is still roughly the same, you need to use the logic that calculates those other values, and use it to calculate the final one.

DileepkumarReddy 23-Aug-12 8:11am

How can i do this more dynamically...how to make more shorter

Christian Graus 23-Aug-12 8:14am

It's a disaster, not least b/c all your columns are nullable. Just store 0, it's the right value, isn't it ? Once you get rid of the IsNull calls, you could define functions for some of these calculations to make it more readable, but basically you need to fix your database design, I think. Once you've done all that, I am not sure your total amount is ALL the columns added at all. I think you need to rethink that logic and write more SQL to calculate it properly, in the same way you calculated all of this