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