CODEWITHSUNDEEP
sql
Simen Thorbjørnsrud
Simen Thorbjørnsrud

Reputation: 21

Running total per account per month

I have created the following table: 

create table AccountBalances (
    Year varchar(255),
    Period varchar(255),
    Account numeric,
    Amount numeric
)

Insert into AccountBalances(Year, Period, Account, Amount)
    Values ('2014', '1', '1210', '100'),
           ('2014', '1', '1210', '200'),
           ('2014', '3', '1210', '100')

Now I want to calculate the running total for each period per account. However may main issue is calculating running totals for periods without transactions. I want my result like this:

Year     Period     Account     Amount
2014        1         1210        300
2014        2         1210        300
2014        3         1210        400

From the transaction table period 2 is not present, but I want to calculate the running total for this period in my running total table.

Upvotes: 2

Views: 9090

Answers (3)

user3104783
user3104783

Reputation: 19

Another approach would be to use a separate Period_lookup table having all possible periods (Months) for a year.

Then we could do an outer join and apply the IsNull / Coalesce function in the select to return the desired results.

Upvotes: 0

Stuart J Cuthbertson
Stuart J Cuthbertson

Reputation: 438

A system-neutral (works on all database systems and versions) and widely used approach to problems like this, is to maintain a Date table containing one row for every day of every year that your application or analysis is interested in.

I believe some database systems can generate something like this on-the-fly, but a stored table takes up very minimal space (365 or 366 rows per year - nothing to a relational database) and can be used in a huge variety of situations. Populate it once for a long number of years into the future and it will serve you well.

For your particular case, you'd want this Date table to include both Year and Period columns: you need to determine how periods are defined, e.g. is that the same as a month or something else like 4-week chunks of time?

Then you can do a query along these lines (please treat as pseudocode - your exact requirements may vary):

with cteAllPeriods as (
    select      distinct
                Year
                ,Period
    from        Date
    where       Year >= 2014    -- Whatever suitable restrictions here
    )
select      D.Year
            ,D.Period
            ,AB.Account
            ,sum(AB.Amount)
from        cteAllPeriods   D
                                -- Assuming this is a running total 
                                -- from the start of the calendar year
left join   AccountBalances AB  on D.Year=AB.Year
                                and D.Period>=AB.Period -- Gets everything UP TO the current period

group by    D.Year
            ,D.Period
            ,AB.Account

Upvotes: 2

gbn
gbn

Reputation: 432261

From SQL Server 2012 onwards you can do this

SELECT 
    *, 
    SUM(Amount) OVER (PARTITION BY Year, Account ORDER BY Period RANGE UNBOUNDED PRECEDING) 
FROM 
    AccountBalances

Upvotes: 2

Related Questions