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