Announcement

Collapse
No announcement yet.

SUM OVER PARTITION or LATERAL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SUM OVER PARTITION or LATERAL

    Hi there,

    I'm not sure what's the best method to obtain a running total but any working method would be great. I've seen many examples but none for exactly what I need.
    I have a table that has the first 3 columns with data. I'd like to calculate the 4th column, which is a running total of order qty for the part, regardless of the order #.
    I need to retain the arrival sequence of the records (or sorted by order #, part #). My attempts at this seem to reorder the data by part # or I get a total order qty and not running total.
    Order # Part # Order Qty Running Total of Order Qty for Part
    11111 AAAAA 1 1
    11111 BBBBB 2 2
    11111 CCCCC 3 3
    22222 BBBBB 4 6
    22222 CCCCC 5 8
    33333 AAAAA 6 7
    Thank you.

    Glenn Gundermann

  • #2
    I think I figured it out:

    with t as (
    select order_no, part_no, order_qty,
    sum(order_qty) over(partition by part_no order by order_no) as running_total_part_order_qty
    from orders)
    select * from t order by order_no, part_no;​

    Comment


    • #3
      The requirements got trickier and I'm wondering if anyone knows how to do this:
      Given the following, assume all of these order lines are for the same part. The Qty Available is from the part master.
      The sum over partition statement above will produce the Running Total you see below.
      Order Qty Valid Running Total Qty Available Result
      500 Y 500 1100 Ship
      500 Y 1000 1100 Ship
      500 Y 1500 1100 Cancel
      1 N 1501 1100 Cancel
      1 Y 1502 1100 Ship

      The third line doesn't have enough qty available so will not ship. I'd like the running total to remain at 1000.
      The fourth line is not valid so I'd like the running total to remain at 1000.
      The fifth line has enough qty available so will ship. I'd like the running total to show 1001.
      I'm basically looking for the running total to only include lines if it doesn't exceed the qty available and meet the condition valid = Y.
      Is this doable using sum over partition, lateral, sub-query, or what?

      Comment


      • #4
        I don't know, but if your question is strictly SQL based you might try this forum.
        https://www.sqlservercentral.com/forums

        Keep in mind syntax and options are likely to be a bit different between IBM SQL and MSSQL

        Comment

        Working...
        X