?
Solved

running total that resets every January 1st in SQL

Posted on 2015-01-08
3
Medium Priority
?
88 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 79

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 2000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

770 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