CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2008-r2
Jay
Jay

Reputation: 465

SQL help calculating Opening and Closing balance columns

I'm using SQL 2008 R2. I have a small table with the following columns:

Date Location1 Location2 Location3

And looking to calculate these 2 columns

Opening Balance = starts at 0, after that equals closing balance from prev day Closing balance = opening balance + location1 + location2 + location3

So I'm basically looking to spit this out into a report:

DATE |  OPENING_BALANCE  | LOC1   | LOC2    |   LOC3    |  CLOSING_BALANCE
----------------------------------------------------------------------------
1/1     $0.00              $1,000    $1,000       $1,000        $3,000 
1/2     $3,000             $2,000    $2,000       $1,000        $8,000 
1/3     $8,000            ($1,000)  ($4,000)      $500          $3,500 
1/4     $3,500            $10,000   ($5,000)      $20,000       $28,500 
1/5     $28,500

Is there an easy way to calculate these 2 columns? Help appreciated! Thanks in advance

** apologies for the sloppy table example

Upvotes: 1

Views: 2779

Answers (1)

Andrei Hirsu
Andrei Hirsu

Reputation: 696

Ok, from the information you provided (only output and no table schema), this query might work for you:

WITH cte AS
(
SELECT
    [Date],
    Loc1 = Location1,
    Loc2 = Location2,
    Loc3 = Location3,
    DayTotal = Location1 + Location2 + Location3,
    ROWNUM = ROW_NUMBER() OVER (ORDER BY [Date])
FROM
    tbl
)
SELECT 
    DATE = c1.[Date],
    OPENING_BALANCE = ISNULL(c2.OpeningDayTotals, 0),
    LOC1 = c1.Loc1,
    Loc2 = c1.Loc2,
    Loc3 = c1.Loc3,
    CLOSING_BALANCE = ISNULL(c2.OpeningDayTotals, 0) + c1.DayTotal
FROM 
    cte c1
OUTER APPLY
(
    SELECT
        OpeningDayTotals = SUM(c2.DayTotal)
    FROM
        cte c2
    WHERE
        c1.ROWNUM > c2.ROWNUM
) AS c2

Upvotes: 4

Related Questions