SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding and Subtracting from Two Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

warrend
Starting Member

USA
15 Posts

Posted - 04/12/2013 :  14:36:33  Show Profile  Reply with Quote
I am using SQL2005. I need to add the Fuel and Linehaul below then subtract that subtotal from TotalCharge below to get the Accessorial costs from tables.

SELECT TOP 100
ORD.ord_hdrnumber AS ProNum,
ref_number AS SAPNum,
ord_charge AS Linehaul,
ivd_charge AS Fuel,
'' AS Accessorial,
ord_totalcharge as TotalCharge
FROM referencenumber REF, orderheader ORD, invoicedetail DTL
WHERE ref_type = 'SAP#' AND
REF.ord_hdrnumber = ORD.ord_hdrnumber AND
ORD.ord_hdrnumber = DTL.ord_hdrnumber AND
ord_invoicestatus IN ('PPD', 'XIN') AND
ord_revtype1 = 'INT' AND
ord_status = 'CMP' AND
DTL.cht_itemcode = 'FUEL'

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1643 Posts

Posted - 04/12/2013 :  18:17:41  Show Profile  Reply with Quote
Hope this helps...

SELECT TOP 100
ORD.ord_hdrnumber AS ProNum,
ref_number AS SAPNum,
ord_charge AS Linehaul,
ivd_charge AS Fuel,
'' AS Accessorial,
ord_totalcharge as TotalCharge,
ord_totalcharge - (ord_charge + ivd_charge) Accessorial

FROM referencenumber REF, orderheader ORD, invoicedetail DTL
WHERE ref_type = 'SAP#' AND
REF.ord_hdrnumber = ORD.ord_hdrnumber AND
ORD.ord_hdrnumber = DTL.ord_hdrnumber AND
ord_invoicestatus IN ('PPD', 'XIN') AND
ord_revtype1 = 'INT' AND
ord_status = 'CMP' AND
DTL.cht_itemcode = 'FUEL'

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/13/2013 :  02:08:06  Show Profile  Reply with Quote
if they're nullable fields then it should be

coalesce(ord_totalcharge,0) - (coalesce(ord_charge,0) + coalesce(ivd_charge,0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

warrend
Starting Member

USA
15 Posts

Posted - 04/15/2013 :  14:14:45  Show Profile  Reply with Quote
Just tried both, neither work. The message I am getting is "Msg 208, Level 16, State 1, Line 1 Invalid object name 'referencenumber'."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/15/2013 :  14:19:34  Show Profile  Reply with Quote
check if you're in the right database and also you've a table called referencenumber in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

warrend
Starting Member

USA
15 Posts

Posted - 04/16/2013 :  07:55:12  Show Profile  Reply with Quote
That was it and I feel silly I didn't notice it before. Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/16/2013 :  10:32:28  Show Profile  Reply with Quote
dont worry it happens to all
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000