Solved

Update a running total

Posted on 2014-04-15
3
170 Views
Last Modified: 2014-04-15
MS SQL Server 2008.

I have a table that has the following columns

visitdate date
Eye int
Treatmentcount int
RunningTotal int

where Eye can be null, 1(right) or 2(left).  Null means no treatment on this date.

Treatmentcount can be null, or a number less than 10

The table is ordered desc on Visitdate.

Visit date   Eye           TreatmentCount   RunningTotal
2012-05-01   1              2
2012-04-01                 
2012-03-01   1              4
2012-02-01   1              1

Open in new window

How can i update the RunningTotal column so it shows the total number of treatments at each visit?
Visit date   Eye           TreatmentCount   RunningTotal
2012-05-01   1              2                      7
2012-04-01                 
2012-03-01   1              4                      5
2012-02-01   1              1                      1

Open in new window

0
Comment
Question by:soozh
3 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40000983
on which basis you wanto calcutae running total ?
0
 

Author Comment

by:soozh
ID: 40001010
not sure what you mean, but you can see the running total in the second table.
0
 
LVL 13

Accepted Solution

by:
sameer2010 earned 500 total points
ID: 40001213
Try this if you meant adding totals based on dates
drop table #t
create table #t(visitdate date,
Eye int,
Treatmentcount int,
RunningTotal int)
insert into #t values('2012-05-01',1,2,null)
insert into #t values('2012-04-01',null,null,null)
insert into #t values('2012-03-01',1,4,null)
insert into #t values('2012-02-01',1,1,null)

select visitdate,Eye,Treatmentcount,(select sum(treatmentcount) from #t b where b.Eye=a.Eye and b.visitdate <=a.visitdate) as runningtotal
from #t a

update #t set RunningTotal = (select sum(treatmentcount) from #t b where b.Eye=a.Eye and b.visitdate <=a.visitdate)
from #t a

Open in new window

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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