I have the following sample dataset

The opening balance is a fixed amount from the previous day. What i want is to use it to start computing the running balance from the first row. The desired output should be as follows

The script i have written so far is only able to do a running total on the trans amount without factoring the opening balance on the first row

    SELECT SUM([trans amount]) OVER (PARTITION BY id ORDER BY id) from dbo.table1

How do i achieve the intended results

CodePudding user response：

I was originally going the same direction as lemon's answer, but figured a way that you only add [Opening Balance] from the first row would be useful as well.

You can make a CTE with a case that will add the first row to get your new total, and then the rest of the original values, and then sum that new column:

with tbl as
(
    select ID
        , Type
        , [Opening Balance]
        , [Trans Amount]
        , case
            when ROW_NUMBER() over (order by ID) = 1 then [Opening Balance]   sum([Trans Amount]) over (order by ID)
            else [Trans Amount]
          end [New Trans Amount]
    from orig_tbl
)
select ID
    , Type
    , [Opening Balance]
    , sum([New Trans Amount]) over (order by ID) [Running Total]
from tbl

What's nice about this way is that your subsequent opening balance can vary without affecting [Running Total].

However, if [Opening Balance] does not vary, then lemon's version is simpler.

CodePudding user response：

It is sufficient to add up the Opening Balance value to your window function result:

SELECT *,
       [Opening Balance]   SUM([Trans Amount]) OVER(ORDER BY [ID]) AS [Running Total]
FROM table1

Partitioning inside the window function is not necessary as long as you have unique values for the ID field (at least in the input sample).

Try it here.

Note: I have assumed you're using SQL Server by looking at your query. If that's not the case, please indicate it into your post as a tag.