Using MS SQL - 2012 I am trying to run a SQL Query that ranks rows by product id's. Then calculates a sum that represents the rows ranked less than or equal to it. I set up a formula to do this and it works but is slow and resource expensive.
My gut feeling is there is a CTE or Rank function that would do what I want - I just don't know about it.
If a product has 4 rows, using the rank I could then recalculate a sum of lesser rows Qty's.
Product Order Qty Rank Sum
Apple John 5 1 5
Apple Josh 2 2 7
Apple Jacob 1 3 8
Apple Jennifer 1 4 9
Row 1 Sum is equal to (Row 1 Qty)
Row 2 Sum is equal to (Row 1 Qty , Row 2 Qty)
Row 3 Sum is equal to (Row 1 Qty , Row 2 Qty , Row 3 Qty )
Row 4 Sum is equal to (Row 1 Qty , Row 2 Qty , Row 3 Qty , Row 4 Qty )
Any help on this would be much appreciated.
Thanks in advance. Josh
If i am understanding this correctly I am thinking you want this. Also this provides sample code, if the first assumptions are wrong please comment.