asked on

Subtract One Column From another In SQL

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

ASKER CERTIFIED SOLUTION

RGBDart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

qasim_md

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

qasim_md

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