June 26, 2004 at 12:36 pm
Here is the recordset I am working with:
partno fjobno foperno prodcost matlcost
-------- ---------- ------- --------------------- ----------------------------------------
6000-B20 00017-0000 10 19.11000 15.0000000000
6000-B00 00017-0000 20 15.00000 .0000000000
6000-000 00017-0000 30 30.65000 .0000000000
The table that references the matlcost only references for the lowest foperno, (foperno = 10). All the remaining matlcost must be calculated from the previous foperno's (prodcost + matlcost).
I have a select staement that will accomplish what I need, but I must run it x amount of times, incrementing a temp table reference each time I execute the statement. Seeing that some of our parts have 8 foperno, I would need to run the select statement 9 times to complete the fmatlcost calculations. Here is the select statement I am using.
select t1.PartNo, t1.fjobno, t1.foperno, t1.prodcost, isnull(t2.prodcost + t2.matlcost, t1.matlcost) as matlcost
into #wip3
from #wip2 t1
left join #wip2 t2 on t1.fjobno = t2.fjobno
and t2.foperno = t1.foperno -10
I then increment the temp table names +1 each and run the select statement again, until all matlcosts are calculated.
I was thinking about using a while loop, by incrementing a table variable, but I understand you can not use table variables with a INTO clause. Then I thought about a CURSOR, but was discouraged by all the articles stating to avoid cursors. So, I could use some insight and/or guidance on how to accomplish what I need to, better than I have figured. The results should be like the following:
partno fjobno foperno prodcost matlcost
-------- ---------- ------- --------------------- ----------------------------------------
6000-B20 00017-0000 10 19.11000 15.0000000000
6000-B00 00017-0000 20 15.00000 34.1100000000
6000-000 00017-0000 30 30.65000 49.1100000000
Where the upper foperno's matlcost equals (prodcost + matlcost) of the previous foperno.
Thanks
Doug
June 27, 2004 at 5:10 pm
The following will produce the desired results without the the need for a loop. It works on your example record set, but I'm not sure if it's what you want for a more comprehensive record set.
select t1.PartNo, t1.fjobno, t1.foperno, t1.prodcost,
matlcost = matlcost + (select isnull(sum(t2.prodcost+t2.matlcost),0)
from #wip2 t2
where t1.fjobno = t2.fjobno
and t2.foperno < t1.foperno
)
from #wip2 t1
order by t1.fjobno, t1.foperno
Cheers,
- Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply