>>>Do not have time to study this papyrus.
>>>However, this join is certainly not a great solution.
>>>

>>>		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?
>>
>>Let's see on this example:
>>

>>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.
>>
>>However, if I need to cross tiers, I can not figure out the JOINs correctly and that's why I am using slow way with the Numbers table (my colleague's original idea).
>>
>>I just ran a test on a big database and it looks like in that database for the category I was using for my tests the sales quantity were 1,2,3 max, so I never crossed multiple tiers and I got my result in 12 seconds which I consider a big achievement.
>
>To implement a fast solution you need a ordered update ( like dBase Replace command ),
>because SQL is declarative and update doesn't support ORDER BY clause,
>a good SQL solution is not simple to found.
>
>My approach would be to find a solution with dBase,
>and then see to rewrite it in SQL after.

I want this to be done on SQL Server, not in dBase. Anyway, for small sold quantities (which is the majority of cases), it falls into 1 tier and the peformance is acceptable.