Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Subtract One Column From another In SQL

Posted on 2011-03-25
3
Medium Priority
?
917 Views
Last Modified: 2012-05-11
Hi All

I have the following view in sql

SELECT     (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
                      AS it_total_gbp, it_doc, it_anal, it_quan, it_qtyinv, (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_Invoice_Value
FROM         dbo.itran

what i would like to do is subtract IT_Total_Invoice_Value from IT_Total_GBP and have it on its own column called Outstanding Value.

Pretty New to sql I Am hoping that it is relatively Easy.

Thanks

John
0
Comment
Question by:pepps11976
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
RGBDart earned 1000 total points
ID: 35214218
There are two options

1. Outside your view:
select  it_total_gbp, it_doc, it_anal, it_quan, it_qtyinv, it_total_Invoice_Value, it_total_gbp - it_total_Invoice_Value  as [Outstanding Value]
from [your_view_name_here]

2. Inside your view

SELECT    
      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp,
      it_doc, it_anal, it_quan, it_qtyinv,
      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_Invoice_Value,
      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) - (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) as [Outstanding Value]      
FROM dbo.itran
0
 
LVL 4

Assisted Solution

by:qasim_md
qasim_md earned 1000 total points
ID: 35214238
Try this::::

SELECT     (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
                      AS it_total_gbp
                      , it_doc, it_anal, it_quan, it_qtyinv,
                      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_Invoice_Value
((CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
-                       (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_Invoice_Value) as Outstanding_Value
FROM         dbo.itran
0
 
LVL 4

Expert Comment

by:qasim_md
ID: 35214277
sorry try this, let me know if it helped ???

SELECT     (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
                      AS it_total_gbp
                      , it_doc, it_anal, it_quan, it_qtyinv,
                      (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_Invoice_Value,
((CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END)
-                       (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100)
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_Invoice_Value) as Outstanding_Value
FROM         dbo.itran
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question