With the code below I was originally attempting to not use sub queries to eliminate "duplicate data" (its technically not duplicate data since each order has several lines of data where prices and weights are different but I wanted to consolidate my results to where I only get distinct order #'s) But with having no luck of only using join conditions and SUM/GROUP BY. I adopted in my first join a sub query to have distinct orders returned with the fields "price" and "shipped". And that worked accordingly and I received the orders I needed. But with utilizing the whole script I realized "carton_no" was causing "duplicate" results being returned I so attempted to add another sub query but I seem to be receiving the error:
Msg 107, Level 16, State 2, Line 17 The column prefix 'TCT' does not match with a table name or alias name used in the query.
Block of code providing errors:
LEFT OUTER JOIN
(
SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK)
INNER JOIN tdc_carton_tx TCT
ON TRCB.order_no = TCT.order_no
AND TRCB.order_ext = TCT.order_ext
AND TRCB.carton_no = TCT.carton_no
GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
) TRCB ON TRCB.order_no = TCT.tdc_carton_tx
DECLARE @location VARCHAR(10)
DECLARE @dateshipped DATETIME
SET @location = 'RAS 2'
SET @DateShipped = DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())-3)
SELECT
SUM (L.Shipped ) AS Shipped
, SUM (L.Price ) AS Price
, O.Order_no
, O.Ext
, O.Cust_Code
, O.Ship_To_Name
, O.Freight_Allow_Type
, TCT.Carton_no
, TPM.dim_ext_x
, TPM.dim_ext_y
, TRCB.Number_of_Boxes
, MAX(TRCB.Skid_Height_Inches )
, MAX(O.Routing) AS 'Routing'
, MAX(O.User_def_fld9) AS 'bol_no'
, MAX(Ship_to_name) AS Ship_to_name_1
, MAX(Ship_to_add_3) AS Ship_to_add_3
, MAX(L.[location]) AS 'Location'
, MAX(O.cust_code ) AS 'Cust_code_1'
, MAX(O.user_def_fld10) AS 'Skids'
, MAX(O.user_def_fld12) AS 'Weight'
, ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_Freight'
, TCT.[weight] + TPM.[weight] AS 'Total_Pallet_Weight'
FROM orders O WITH (NOLOCK)
INNER JOIN
(
SELECT o.order_no, L.shipped, L.price FROM orders O WITH (NOLOCK) INNER JOIN Ord_list L ON O.order_no = L.order_no
WHERE O.routing NOT LIKE 'FEDX%'
AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3')
AND ISNULL(O.void,'') <> 'V'
AND L.location = @location
GROUP BY O.order_no, L.shipped, L.price
)
L ON O.order_no = L.order_no
JOIN xfers X
ON O.order_no = X.xfer_no
JOIN xfer_list XL
ON X.xfer_no = XL.xfer_no
--INNER JOIN ord_list L WITH (NOLOCK)
-- ON O.order_no = L.order_no
-- AND O.ext = L.order_ext
INNER JOIN arcust C WITH (NOLOCK)
ON O.cust_code = C.customer_code
LEFT OUTER JOIN armaster A WITH (NOLOCK)
ON A.customer_code = O.cust_code
AND A.ship_to_code = O.ship_to
AND A.location_code = O.location
LEFT OUTER JOIN tdc_pkg_master TPM WITH (NOLOCK)
ON TCT.carton_type=TPM.pkg_code
INNER JOIN tdc_carton_tx TCT WITH (NOLOCK)
ON TPM.pkg_code = TCT.carton_type
LEFT OUTER JOIN
(
SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK)
INNER JOIN tdc_carton_tx TCT
ON TRCB.order_no = TCT.order_no
AND TRCB.order_ext = TCT.order_ext
AND TRCB.carton_no = TCT.carton_no
GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
) TRCB
ON TRCB.order_no = TCT.tdc_carton_tx
LEFT OUTER JOIN ras_bol_details BD WITH (NOLOCK)
ON X.xfer_no = BD.bl_src_no
AND X.xfer_no = BD.bl_src_no AND BD.order_type IN ('T','S')
LEFT OUTER JOIN ras_bol_header BH WITH (NOLOCK)
ON BD.bl_no = BH.bl_no
AND O.order_no = BD.bl_src_no
AND O.ext = BD.bl_src_ext
WHERE DATEADD(day, 0, DATEDIFF(day, 0, O.date_shipped)) >= @dateshipped
GROUP BY
O.order_no
, O.ext
, O.cust_code
, O.ship_to_name
, O.freight_allow_type
, TCT.carton_no
, TCT.[weight]
, TPM.[weight]
, TPM.dim_ext_x
, TPM.dim_ext_y
, TRCB.number_of_boxes
, TRCB.skid_height_inches
dd
. Your life won't change by spelling it out (day
is just one extra character), and you can avoid a lot of problems (see here and here).TCT
alias in both the main query and in a sub-query. I'd trying changing one or the other, just to make sure that the multiple references aren't confusing either the engine, or you!