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