Solved

Subtract One Column From another In SQL

Posted on 2011-03-25
3
845 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
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
RGBDart earned 250 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 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now