title: Calculating cumulative summation using SQL based on Product Number
body: <p>I have wrote an SQL query which allocates products to a customer based on the quantity remaining for each product in stock.</p>
  
  <p>I.e. If OurQty = 55 and there are 70 outstanding orders split between 5 customers (Customer 1 = 35, Customer 2 = 10, Customer 3 = 3, Customer 4 = 16, Customer 5 = 6) then my SQL query will allocate the reserved product quantity to each customer sequentially until OurQty = 0 or all of the outstanding orders have been fulfilled.</p>
  
  <p>My SQL query:</p>
  
  <pre><code>WITH allocate 
       AS (SELECT rd.ponum                                  AS PONum, 
                  od.orderline                              AS OrderLine, 
                  rd.partnum                                AS PartNum, 
                  p.partdescription                         AS PartDescription, 
                  od.ordernum                               AS OrderNum, 
                  rd.ourqty                                 AS OurQty, 
                  od.number01                               AS Reserved, 
                  rd.ourqty - od.number01                   AS NewOurQty, 
                  c.custnum                                 AS CustNum, 
                  c.name                                    AS Name, 
                  ( rd.ourqty - Sum(od.number01) 
                                  OVER ( 
                                    ORDER BY od.ordernum) ) AS CumulativeSum 
           FROM   dbo.rcvdtl AS rd WITH (nolock) 
                  INNER JOIN dbo.part AS p WITH (nolock) 
                          ON rd.partnum = p.partnum 
                  INNER JOIN dbo.orderdtl AS od WITH (nolock) 
                          ON rd.partnum = od.partnum 
                  INNER JOIN dbo.orderhed AS oh WITH (nolock) 
                          ON od.ordernum = oh.ordernum 
                  INNER JOIN dbo.customer AS c WITH (nolock) 
                          ON od.custnum = c.custnum 
           WHERE  ( rd.ponum = 73 ) 
                  AND ( od.number01 &gt; 0 ) 
                  AND ( od.openline = 1 )) 
  SELECT ponum, 
         orderline, 
         partnum, 
         partdescription, 
         ordernum, 
         ourqty, 
         reserved, 
         newourqty, 
         custnum, 
         name, 
         cumulativesum + reserved AS 'Left In Stock', 
         CASE 
           WHEN ( cumulativesum + reserved ) - reserved &gt; 0 THEN reserved 
           ELSE ( CASE 
                    WHEN ( cumulativesum + reserved ) &gt; 0 THEN 
                    ( cumulativesum + reserved ) 
                    ELSE 0 
                  END ) 
         END                      AS Allocated 
  FROM   allocate 
  </code></pre>
  
  <p>This works fine when the 'PartNum' is specified as a parameter. The problem arises when I query the table without specifying the 'PartNum' since the table contains multiple 'PartNums' which affects the accuracy of the 'CumulativeSum' which is used to check if (OurQty &lt; CumulativeSum) and as such prevent the allocation of stock which does not physically exist.</p>
  
  <p>My original idea was to pass the database table the 'PartNum' as a parameter for a given 'PONum' iteratively and then merge the results into one dataset on my application. Unfortunately the program I am using which calls the SQL query cannot pass the 'PartNum' to the SQL query as a parameter.</p>
  
  <p>Therefore, Is it possible to calculate the cumulative sum for each PartNum in the table using SQL?</p>
  
  <p><strong>I am using Microsoft SQL Server 2012</strong></p>