Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding and Subtracting from Two Tables

Author  Topic 

warrend
Starting Member

15 Posts

Posted - 2013-04-12 : 14:36:33
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
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-04-12 : 18:17:41
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

52326 Posts

Posted - 2013-04-13 : 02:08:06
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

15 Posts

Posted - 2013-04-15 : 14:14:45
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

52326 Posts

Posted - 2013-04-15 : 14:19:34
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

15 Posts

Posted - 2013-04-16 : 07:55:12
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

52326 Posts

Posted - 2013-04-16 : 10:32:28
dont worry it happens to all
welcome

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

- Advertisement -