SELECT 
  orders.orderid, 
  CONCAT(
    orders.shipfirstname, ' ', orders.shiplastname
  ) AS ToName, 
  MAX(orders.shipcompanyname) AS ToCompany, 
  orders.shipaddress1 AS ToAddress1, 
  orders.shipaddress2 AS ToAddress2, 
  MAX(orders.shipcity) AS ToCity, 
  MAX(orders.shipstate) AS ToState, 
  MAX(orders.shippostalcode) AS ToPostalCode, 
  CASE WHEN MAX(orders.shipstate) = 'GU' THEN 'Guam' ELSE MAX(orders.shipcountry) END AS ToCountry, 
  MAX(od.productcode) AS Productcode, 
  CASE WHEN MAX(orders.shipphonenumber) IS NULL 
  OR MAX(orders.shipphonenumber) IS NULL 
  OR MAX(orders.shipphonenumber) = '' THEN '8662432721' ELSE MAX(orders.shipphonenumber) END AS ToPhoneNumber, 
  CASE WHEN MAX(orders.shippostalcode) LIKE '88[0-3]%' 
  AND MAX(orders.shippingmethodid) NOT IN (141, 142, 950, 951, 952) THEN 777 WHEN MAX(orders.shippostalcode) LIKE '879%' 
  AND MAX(orders.shippingmethodid) NOT IN (141, 142, 950, 951, 952) THEN 777 WHEN MAX(orders.shippostalcode) LIKE '79[0-9]%' 
  AND MAX(orders.shippingmethodid) NOT IN (141, 142, 950, 951, 952) THEN 777 WHEN MAX(orders.shippostalcode) LIKE '7[3-6][0-9]%' 
  AND MAX(orders.shippingmethodid) NOT IN (141, 142, 950, 951, 952) THEN 777 WHEN MAX(orders.shippostalcode) LIKE '71[0-2]%' 
  AND MAX(orders.shippingmethodid) NOT IN (141, 142, 950, 951, 952) THEN 777 WHEN MAX(orders.shippostalcode) LIKE '714%' 
  AND MAX(orders.shippingmethodid) NOT IN (141, 142, 950, 951, 952) THEN 777 WHEN MAX(orders.shippostalcode) LIKE '718%' 
  AND MAX(orders.shippingmethodid) NOT IN (141, 142, 950, 951, 952) THEN 777 WHEN MAX(orders.shippingmethodid) = 0 
  AND MAX(orders.order_entry_system) = 'MARKETPLACE' THEN 301 ELSE MAX(orders.shippingmethodid) END AS ShippingMethodID, 
  MAX(Customs.DeclaredValue) AS DeclaredValue, 
  0.00 AS Weight 
FROM 
  orders 
  JOIN orderdetails od ON orders.orderid = od.orderid 
  JOIN (
    SELECT 
      od.orderid, 
      SUM(od.totalprice) AS DeclaredValue 
    FROM 
      orderdetails od 
    WHERE 
      od.productcode NOT LIKE ('DSC-%') 
      AND (
        od.qtyonpackingslip IS NULL 
        OR od.qtyonpackingslip = 0
      ) 
    GROUP BY 
      od.orderid
  ) AS Customs ON orders.orderid = Customs.orderid 
WHERE orders.OrderID IN 
(8554351,8558783,8568116,8551355,8564351,8544671,8558783,8554325)
GROUP BY orders.OrderID
ORDER BY orders.OrderID ASC