Listing 3: Solution 3 Using OVER and a Join

SELECT SalesOrderID AS orderid, SalesOrderDetailID AS line,
  CASE SalesOrderDetailID
    WHEN MIN(SalesOrderDetailID) OVER(PARTITION BY SalesOrderID) THEN 'first'
    WHEN MAX(SalesOrderDetailID) OVER(PARTITION BY SalesOrderID) THEN 'last'
    ELSE 'mid'
  END AS pos, ProductID AS productid, OrderQty AS qty, 
  CAST(1.*OrderQty/SUM(OrderQty) OVER(PARTITION BY SalesOrderID)*100
       AS DECIMAL(5, 2)) AS qtyper,
  LineTotal AS val,
  CAST(LineTotal/SUM(LineTotal)  OVER(PARTITION BY SalesOrderID)*100
       AS DECIMAL(5, 2)) AS valper
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659, 43660)
ORDER BY orderid, line;