Solved

running total that resets every January 1st in SQL

Posted on 2015-01-08
3
86 Views
Last Modified: 2015-01-10
I have a table with transactions and a date column

Date                     Amount
01-jan-14             400
02-Feb-14            100
05-May-14           200
04-Jan-15             600
05-Apr-15            300
02-Jan-16             150
05-Jun-16             300

I want to write a query in sql that will give me the following

Date                     Amount     Balance
01-jan-14             400              400
02-Feb-14            100              500
05-May-14           200              700
04-Jan-15             600              600   - resets each year
05-Apr-15            300              900
02-Jan-16             150              150  - resets each year
05-Jun-16             300              450

any code suggestions?
0
Comment
Question by:RDLFC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 40538418
you can use case/conditional to compare the year
declare @year as int
declare @balance as int
set @year=0
set @balance=0
select date, amount,
( conditional check that will reset @balance=0 if  year from date is different from @year that was assigned in a prior check .
 if year(date) <> @year
              @balance=amount;
else
     @balance=@balance+amount

@balance
) as balance
from transaction order by date

did not work it out but the logic is there.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40538768
If the data becomes static after a certain period, I suggest storing the running_total in the row itself for the static data, and only recomputing still potentially changing data on the fly.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40539560
select
*
from table1
cross apply (
  select sum(t2.amount) as balance
  from table1 t2 
  where t2.adate <= table1.adate and year(t2.adate) = year(table1.adate)
  ) ca1

Open in new window


nb: I used the fieldname [adate]  as date is a reserved word
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question