I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty. i.e.
- The first data of qoh is the same of the first row's value of rel_qty.
- Second value of qoh should be sum of first and second rows' value of rel_qty.
- Third value of qoh should be sum of first, second and third rows' value of rel_qty.
is there any possibilities to do this?
Here is my query. but this query satisfied only first and second rows not more that that,
SELECT a.id, a.tot_qty, a.rel_qty, (a.tot_qty - COALESCE (a.rel_qty + (SELECT b.rel_qty FROM dbo.foo AS b WHERE (b.id = a.id - 1)), a.rel_qty) ) AS qoh FROM dbo.foo AS a ORDER BY a.id
And if my id skip one value this query failed to do the calculation. How can I fix this?
here is screenshot of my result