> FROM #Sales S > INNER JOIN dbo.numbers N on N.number > 0 > AND N.number <= ABS(S.Sold) >I know it's not good, but I don't have an idea how to fix it :( E.g. how to match correctly if the #sales go into multiple #CostTiers?
CostTiers Item units_from units_thru unit_cost W 1 100 $1 W 101 110 $2 W 111 120 $2.5 ---------------- Sales Item Sold RemovedPrior W 90 0 W 50 90 -- this should go into 3 tiers W 70 140 -- selling items we didn't have in the inventory -- need to get the last cost W -30 210 -- this was a return -- again last cost as we're already in the negative on-hand -----------------------------------------------------------------------------------This is just to give you an idea. So, if RemovedPrior and RemovedPrior + Sold are between units_from and units_thru I know that this is one tier and that's simple. So, this is what my first select and insert is doing.