Solved

running total that resets every January 1st in SQL

Posted on 2015-01-08
3
78 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
3 Comments
 
LVL 76

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:ScottPletcher
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now