>OK, haven't done one of these in a long time.
>
>This one isn't particularly tough, except perhaps for an unexpected lesson about a certain SQL Server data type.
>
>See the attached screen shot.
>
>Here's the puzzle.
>Using the AdventureWorks database (you can use AdventureWorks, AdventureWorks2008, or AdventureWorks2008R2)
>
>Using 2 tables
>
>Purchasing.PurchaseOrderHeader (and the columns PurchaseOrderID, VendorID, OrderDate, Freight, Total)
>Purchasing.PurchaseOrderDetail (and the columns ProductID, OrderQty, UnitPrice, and LineTotal)
>
>Write a query that produces a result set that allocates the Freight for each PurchaseOrder (in PurchaseOrderHeader) down to the product line items in the PurchasingOrderDetail table. (The two tables are joined by PurchaseOrderID). The allocation will be based on each line item's LineTotal dollars (Qty * UnitPrice) as a ratio/% of the sum of LineTotal across all products (which is the same as the SubTotal column in the header table).
>
>So stated another way.... each line item's ratio is LineTotal (Qty * UnitPrice) divided by the header's subtotal column (or the sum of LineTotal for the order). Take that ratio and allocate the freight downward. (The screen shot shows an example for PurchaseOrderID 19....the result set shows the sum of the allocated total for freight equaling the original freight.)
>
>And that's part of the key of this....after you allocate the freight downward, sum all the allocated freights (across all order line items) and see how closely it matches the sum of freight in the order header table. A hint: depending on how you handle the existing column data types, and how you cast, you could wind up being off by about $170, or off by a fraction of a penny.
>
>Like I said, not the toughest query in the world, but you may run into something interesting with the allocation.
>
>If you're wondering, "why would anyone allocate freight downward", that's true, you might not. It's just a general exercise in allocation and data types.

Check this:

use AdventureWorks
;with Info as (select POH.PurchaseOrderID, POH.VendorID, POH.OrderDate,
POH.SubTotal, POH.Freight, POD.LineTotal, POD.ProductID,
E.LineItemRatio, E.LineItemRatio*POH.Freight as AlloCatedFreight

from Purchasing.PurchaseOrderHeader POH
inner join Purchasing.PurchaseOrderDetail POD
ON POH.PurchaseOrderID = POD.PurchaseOrderID

CROSS APPLY (SELECT (POD.OrderQty * POD.UnitPrice)*1.0/NULLIF(POH.SubTotal,0) as LineItemRatio) E)

select *, SUM(AllocatedFreight) over (partition by PurchaseOrderID) as TotalFreight
from Info
order by PurchaseOrderID

Results match fine. I used implicit conversion (by multiplying on 1.0) just in case.