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.
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 TotalChargeFROM referencenumber REF, orderheader ORD, invoicedetail DTLWHERE 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 100ORD.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) AccessorialFROM referencenumber REF, orderheader ORD, invoicedetail DTLWHERE ref_type = 'SAP#' ANDREF.ord_hdrnumber = ORD.ord_hdrnumber ANDORD.ord_hdrnumber = DTL.ord_hdrnumber ANDord_invoicestatus IN ('PPD', 'XIN') ANDord_revtype1 = 'INT' ANDord_status = 'CMP' ANDDTL.cht_itemcode = 'FUEL'=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-13 : 02:08:06
|
if they're nullable fields then it should becoalesce(ord_totalcharge,0) - (coalesce(ord_charge,0) + coalesce(ivd_charge,0))------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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'." |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-16 : 10:32:28
|
dont worry it happens to allwelcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|