I have two tables (ShipmentHeaders and ShipmentAPAR) where my shipment info is stored in ShipmentHeaders and is controlled by PK_OrderNo. The costs of the shipments are stored in ShipmentAPAR.
What I am after is to run a query based off a customers name and for query to find all the related Order Numbers in the ShipmentHeaders and then pull up the total amount (Sum) of any entries found in ShipmentAPAR table for FK_OrderNo.
To query by a customer name, you will also need info from the table where customer names are stored, presumably along with the customerNo. If you already know the CustomerNo of the customer that you want to query for,
SELECT
SUM(Total) AS [TotalCostOfShipment]
FROM
ShipmentHeader sh
INNER JOIN ShipmentAPAR sa ON
sa.FK_OrderNo = sh.PK_OrderNo
WHERE
sh.CustomerNo = 12345 -- or the customer number of the customer you are interested in.
Thank you for your help so far James. This is the query that I have constructed so far
Use AirTrak
SELECT PK_OrderNo
,Housebill
,OrgAirport
,DestAirport
,CallIn
,CallInName
,Description
,APARType
,Total
,TotalMiles
,OrderDate
,PODDateTime
,ShipmentDateTime
,InvoiceDateTime
From dbo.tbl_ShipmentHeader as SH
Inner Join dbo.tbl_ShipmentAPAR as SC
On SH.PK_OrderNo = SC.FK_OrderNo
where SH.BillNo = 2003 and
SC.APARCode= 'C'
With the query you gave me, would I need to add it in as a sub query to mine to return the results I am after?
For instance, Order Number 4915 has 4 customer charges, so after it displays the 4 entries as predicted, but I want to have a new row that shows the total charges after the listed entries. Here is a snipit of what I get returned currently.
Try using GROUPING SETS, like shown below. You can add additional sets into the grouping sets if you need subtotals.
Use AirTrak
SELECT PK_OrderNo
,Housebill
,OrgAirport
,DestAirport
,CallIn
,CallInName
,Description
,APARType
,SUM(Total) AS Total
,TotalMiles
,OrderDate
,PODDateTime
,ShipmentDateTime
,InvoiceDateTime
From dbo.tbl_ShipmentHeader as SH
Inner Join dbo.tbl_ShipmentAPAR as SC
On SH.PK_OrderNo = SC.FK_OrderNo
where SH.BillNo = 2003 and
SC.APARCode= 'C'
GROUP BY GROUPING SETS
(
(PK_OrderNo
,Housebill
,OrgAirport
,DestAirport
,CallIn
,CallInName
,Description
,APARType
,TotalMiles
,OrderDate
,PODDateTime
,ShipmentDateTime
,InvoiceDateTime
),
()
)