>Hi Naomi,
>
>thanks, but most of those concentrate on geting the "max" record out of a given set. Nice insight anyway.
>
>My problem is that I somehow need to split rather then concentrate.
>
>The given set is
>
>P_IX,iMat,nQty
>4,2,0.2
>5,2,20
>6,2,8
>7,2,22
>
>
>I need to take qty of 25
>
>so return should be
>
>P_IX,iBase,iMat,nQty
>1,7,2,22
>2,5,2,3
>
>or even better
>
>P_IX,iBase,iMat,nQty
>1,7,2,22
>2,6,2,3
>
>
>because base record P_Ix=6 fits better
>
>so I need to slice the qty to be taken into pieces fitting to the amount in the source.
>
>The target take 22 out of record P_Ix=4 of the source and the rest of 3 out of the next.
>
>Agnes
I see - a bit tricky. I solved a similar problem with recursive cte (see
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries - though it doesn't work for best fitting).
I think you may be better with dbase solution here rather than SQL.
If it's not broken, fix it until it is.
My Blog