>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?

Interesting, the queries are almost identical, but the first takes 71% in the execution plan... Oops, I forgot I added a check in my final query as a computed column - if I remove this extra field, it gives the same 50%