Thanks for the reply. upon reading it I realize my sample was not as complete or accurate as I had hoped. Here is the actual code:
SELECTDISTINCT
l.lgh_number,
o.mov_number
FROM orderheader o
INNER JOIN legheader l ON o.mov_number = l.mov_number
WHERE ord_completiondate BETWEEN @startdate AND @enddate
AND ord_revtype1 = @costcenter
AND ord_tractor = ISNULL(@tractor,ord_tractor)
AND stp_event = 'NBS'
--SELECT * FROM #Routes r
CREATE TABLE #Output (lgh_number INT, mov_number INT, Pay_Miles INT, Bill_Miles INT, ord_hdrnumber INT,
Order_Number CHAR(12), Tractor VARCHAR(8), BOL VARCHAR(30), Invoice_Miles FLOAT, FSC_Rate MONEY,
FSC_Charge MONEY, Pyd_Amount FLOAT, Completion_Date DATETIME, Truck_Type VARCHAR(6))
INSERT INTO #Output
( lgh_number ,
mov_number ,
Pay_Miles ,
Bill_Miles)
SELECT DISTINCT
lgh_number,
mov_number,
SUM(stp_lgh_mileage),
SUM(stp_ord_mileage)
UPDATE o SET ord_hdrnumber = (SELECT ord_hdrnumber FROM dbo.orderheader oh WHERE o.mov_number = o.mov_number)
FROM #Output o
UPDATE o SET Order_Number = (SELECT ord_number FROM dbo.orderheader oh WHERE o.mov_number = o.mov_number)
FROM #Output o
UPDATE o SET Tractor = (SELECT ord_tractor FROM dbo.orderheader o
WHERE o.mov_number = o.mov_number)
FROM #Output o