• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

running total that resets every January 1st in SQL

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
RDLFC
Asked:
RDLFC
1 Solution
 
arnoldCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now