Solved

Update a running total

Posted on 2014-04-15
3
173 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
[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 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

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

632 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