Can I avoid a cursor here?
-
I am not sure how to get the result i am looking for without a cursor. Here is my current statement.
SELECT S.OrderNo, R.Step, SUM(D.QtyProduced) AS Produced
FROM tblMpTimeSum S INNER JOIN
tblMpRequirements R ON S.OrderNo = R.OrderNo AND S.ReleaseNo = R.ReleaseNo AND S.ReqID = R.ReqId LEFT OUTER JOIN
tblMpTimeDtl D ON S.TransId = D.TransId
GROUP BY S.OrderNo, R.Step
This is the current result:
Order Step Produced
00002811 010 2295
00002811 020 2510
00002811 030 2524
00002811 040 1920
I would like to see each steps previous Step and Produced on each line. Something like this.
Order Step Produced PrevStep PrevProduced
00002811 010 2295 Null 0
00002811 020 2510 10 2295
00002811 030 2524 20 2510
00002811 040 1920 30 2524
I am not sure where to go from here. Any help would be appreciated.
Thanks,
vmon
-
If Step is always incremented by 10 with no gaps, you could easily just outer join back to tblMpRequirements with something like "R.Step = R2.Step - 10" and join that to tblMpTimeDtl to get your Produced value.
Assuming you can't count on a completely sequential increment for Step (more likely). You could probably use the ROW_NUMBER() function in 2005 to provide a sequential ranking of each row in order of Step and then join in the same manner.
-
another approach could be something like this where you substitute your orignal query in the inner queries:
select o.order_no, o.step, o.produced, max(n.step), max(n.produced)
from
(select order_no, step, produced
from your_original_query) o
left join (select order_no, step, produced
from your_original_query) n
on o.order_no = n.order_no
and o.step <> n.step
and o.step > n.step
group by
o.order_no, o.step, o.produced
-
Chuck this works great, I would not have thought of this approach. I did consider the -10 but I cannot be certain the steps will be uniform by 10's.
Only trouble I am having is the max(n.produced) is repating for steps because produced can go down and it uses the maximum for several steps. Is there a different function than max that I could use?
00003363 010 5124.0000000000 NULL NULL
00003363 020 9304.0000000000 010 5124.0000000000
00003363 030 10585.0000000000 020 9304.0000000000
00003363 040 7610.0000000000 030 10585.0000000000
00003363 050 7007.0000000000 040 10585.0000000000
00003363 060 3781.0000000000 050 10585.0000000000
00003363 070 .0000000000 060 10585.0000000000
Thanks.
-
one more level would do it (replace testing with your original query)
select
x.order_no, x.step, x.produced, x.max_step, orig.produced
from
(
select o.order_no, o.step, o.produced, max(n.step) max_step
from
(select order_no, step, produced
from testing) o
left join (select order_no, step, produced
from testing) n
on o.order_no = n.order_no
and o.step > n.step
group
by
o
.order_no, o.step, o.produced) x
left join testing orig
on x.order_no = orig.order_no
and x.max_step = orig.step
-
This is awsome. I still don't follow it totally. I have a ways to go to catch up with SQL stuff. Thank you very much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply