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

Good job - thanks for giving it a try. The total freight is about 1.5 million, and you're only off by .0077 of a penny. :)

I use this in a class assignment that I give people - as an example of the big gotcha with the money data type.

Unless you either cast it as a decimal (or use the "multiply by 1.0" technique), the scale of the money column is only good to four decimals, which is going to be lousy if you need to do allocations where the aggregate results need to be accurate to a penny or less. If you simply use the existing money data type, the sum of the allocations is off by over $100.

Here's the solution I use - execution plan and IO stats are a bit better (I don't have the worktable). Don't know if I find this interesting or not, but a "cast as decimal" doesn't have any impact (neither for better or worse) compared to a multiply by one.

(And I'm also off by .0077 of a penny)

select *, LineItemRatio * Freight as AllocatedFreight from 
(select POH.PurchaseOrderID, POH.VendorID, POH.OrderDate, 
                  SubTotal, Freight, LineTotal, POD.ProductID, 
                  (OrderQty * UnitPrice) * 1.0  / POH.SubTotal  as LineItemRatio 
from Purchasing.PurchaseOrderHeader  POH
       join Purchasing.PurchaseOrderDetail POD ON poh.PurchaseOrderID = pod.PurchaseOrderID ) TempAlias
ORDER BY PurchaseOrderID 

compute sum( LineItemRatio * Freight)    -- for verification

Thanks....anybody else with a different approach?