Cursor question

I have a table with the following columns Parent_Part (PP), Child_part (CP),
Need_Date (ND), Reqd_qty (RQ), QTY_On_Hand (QOH). QOH is total qty in stock
that has been reserved for the parent part and is to be allocated to the
child based on the need date per PP/CP group. The sample table looks like
this after it's sorted based on allocation criteria. Your help will be
greated appreciated. TIA.
NOTE: I have not been using my hotmail account and I may have to activate
it so for now please post your reply here.
PP CP ND RQ QOH ALLOC
A A1 3/1/05 5 7 ?
A A1 4/1/05 5 7 ?
A A4 3/10/03 1 0 ?
B B3 3/1/05 10 6 ?
B B3 5/15/05 2 6 ?
B B3 6/1/05 2 6 ?
Result should look like this
PP CP ND RQ QOH ALLOC
A A1 3/1/05 5 7 5
A A1 4/1/05 5 7 2
A A4 3/10/03 1 0 0
B B3 3/1/05 10 6 6
B B3 5/15/05 2 6 0
B B3 6/1/05 2 6 0Hi
See http://www.aspfaq.com/etiquett__e.asp?id=5006 on how to make more
useful posts.
This assume that all PP, CP and ND combinations are unique!
SELECT q.PP, q.CP, q.ND, q.RQ, q.QOH,
CASE WHEN q.QOH - r.rq > 0 THEN
CASE WHEN q.QOH - r.rq - q.rq < 0 THEN q.QOH - r.rq
ELSE q.rq
END
ELSE 0
END AS ALLOC
from #qry q
JOIN (
SELECT b.pp, b.cp, b.nd, ISNULL(SUM(a.RQ),0) AS RQ
FROM #qry a
RIGHT JOIN #qry b ON b.pp = a.pp and b.cp = a.cp and a.nd < b.nd
GROUP BY b.pp, b.cp, b.nd
) r ON q.pp = r.pp and q.cp = r.cp and q.nd = r.nd
ORDER BY q.pp, q.cp, q.nd
John
"danlin" wrote:

> I have a table with the following columns Parent_Part (PP), Child_part (CP
),
> Need_Date (ND), Reqd_qty (RQ), QTY_On_Hand (QOH). QOH is total qty in sto
ck
> that has been reserved for the parent part and is to be allocated to the
> child based on the need date per PP/CP group. The sample table looks like
> this after it's sorted based on allocation criteria. Your help will be
> greated appreciated. TIA.
> NOTE: I have not been using my hotmail account and I may have to activate
> it so for now please post your reply here.
> PP CP ND RQ QOH ALLOC
> A A1 3/1/05 5 7 ?
> A A1 4/1/05 5 7 ?
> A A4 3/10/03 1 0 ?
> B B3 3/1/05 10 6 ?
> B B3 5/15/05 2 6 ?
> B B3 6/1/05 2 6 ?
> Result should look like this
> PP CP ND RQ QOH ALLOC
> A A1 3/1/05 5 7 5
> A A1 4/1/05 5 7 2
> A A4 3/10/03 1 0 0
> B B3 3/1/05 10 6 6
> B B3 5/15/05 2 6 0
> B B3 6/1/05 2 6 0
>