Solved

running total that resets every January 1st in SQL

Posted on 2015-01-08
3
87 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 49

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

724 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