CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Shiju Shaji
Shiju Shaji

Reputation: 1730

SQL Server 2008 R2 running sum

I have a table like below.

row_no and product are PK.

+--------+---------+-------+-----+---------------+---------+
| row_no | Product | value | qoh | prev_week_qty | cum_qty |
+--------+---------+-------+-----+---------------+---------+
| 1      | pr:1    | 101   | 101 | NULL          | NULL    |
| 2      | pr:1    | 201   | 101 | NULL          | 100     |
| 3      | pr:1    | 101   | 101 | NULL          | NULL    |
| 4      | pr:1    | 101   | 101 | NULL          | NULL    |
| 5      | pr:1    | 183   | 101 | NULL          | -18     |
| 6      | pr:1    | 101   | 101 | NULL          | NULL    |
| 7      | pr:1    | 101   | 101 | NULL          | NULL    |
| 8      | pr:1    | 149   | 101 | NULL          | -34     |
| 9      | pr:1    | 131   | 101 | NULL          | -18     |
| 10     | pr:1    | 101   | 101 | NULL          | NULL    |
| 11     | pr:1    | 113   | 101 | NULL          | -18     |
| 12     | pr:1    | 101   | 101 | NULL          | NUll    |
| 13     | pr:1    | 101   | 101 | NULL          | NUll    |
| 14     | pr:1    | 101   | 101 | NULL          | NUll    |
| 17     | pr:1    | 101   | 101 | NULL          | NULL    |
+--------+---------+-------+-----+---------------+---------+

Is there any way to implement this without usig cusrsor?

Logic: Value = qoh + cum_qty + prev_week_qty

For ex:

Expected output:

+--------+---------+-------+-----+---------------+---------+
| row_no | Product | value | qoh | prev_week_qty | cum_qty |
+--------+---------+-------+-----+---------------+---------+
| 1      | pr:1    | 101   | 101 | NULL          | NULL    |
| 2      | pr:1    | 201   | 101 | NULL          | 100     |
| 3      | pr:1    | 201   | 101 | NULL          | NULL    |
| 4      | pr:1    | 201   | 101 | NULL          | NULL    |
| 5      | pr:1    | 183   | 101 | NULL          | -18     |
| 6      | pr:1    | 183   | 101 | NULL          | NULL    |
| 7      | pr:1    | 183   | 101 | NULL          | NULL    |
| 8      | pr:1    | 149   | 101 | NULL          | -34     |
| 9      | pr:1    | 131   | 101 | NULL          | -18     |
| 10     | pr:1    | 131   | 101 | NULL          | NULL    |
| 11     | pr:1    | 113   | 101 | NULL          | -18     |
| 12     | pr:1    | 113   | 101 | NULL          | NUll    |
| 13     | pr:1    | 113   | 101 | NULL          | NUll    |
| 14     | pr:1    | 113   | 101 | NULL          | NUll    |
| 17     | pr:1    | 101   | 101 | NULL          | NULL    |
+--------+---------+-------+-----+---------------+---------+

I am using SQL Server 2008 R2.

Upvotes: 0

Views: 58

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93744

In SQL SERVER 2012+ you can use SUM()OVER(ORDER BY) trick unfortunately you are using older version. Try something like this

SELECT *
FROM   Yourtable A
       CROSS apply (SELECT Sum(Isnull([cum_qty], 0)
                               + Isnull(prev_week_qty, 0) + CASE WHEN row_no = 1 THEN qoh ELSE 0 END) su
                    FROM   Yourtable B
                    WHERE  a.[row_no] >= b.[row_no]) cs

Upvotes: 1

Related Questions