December 12, 2011 at 10:39 pm
Some background: My client is using a tool that stores original budget values and then allows a user to make change requests to the budget. The database only stores the original budget $ and most current budget $. However, I need to know what the budget amt was during each change
I need to find a way to calculate it so
ChangeID 2 = ChangeID 1 [BudgetAmt] + ChangeID 1 [ChangeAmt]
ChangeID 3 = ChangeID 2 [BudgetAmt] + ChangeID 2 [ChangeAmt]
and so on
I've attached a screenshot showing a simplified version of the table values and the expected result.
Any thoughts as to a preferred approach for resolving something like this would be much appreciated. Thanks.
December 13, 2011 at 2:14 am
First of all, your requirements don't makes sense to me. It is not so that 15 + NULL = 15. NULL is an unknown value, not 0. But, this is most likely an error in your data model.
What we're dealing with here is called running aggregate, or more specifically running totals. A simple solution to your issue is:
use tempdb;
go
create table Table1 (
ChangeId int,
BudgetAmt int,
ChangeAmt int
);
insert into Table1 values
(1,5,10),
(2,NULL,NULL),
(3,NULL,20),
(4,NULL,30),
(5,NULL,NULL);
select
t1.ChangeId,
(select MAX(BudgetAmt) from Table1)+isnull(SUM(t2.ChangeAmt),0) as BudgetAmt,
t1.ChangeAmt
from
Table1 t1
left join
Table1 t2 on
t2.ChangeId < t1.ChangeId
group by
t1.ChangeId, t1.ChangeAmt
order by
ChangeId
Running totals can be a nightmare with large data sets, fortunately there are coming new features in SQL Server 2012 (framing) making it a lot faster.
December 13, 2011 at 2:30 am
Jeff Moden and others have conducted extensive research into running totals using existing versions of SQL Server, the results of which are published here[/url]. Denali offers a completely new and fully documented method explained here[/url] by Wayne Sheffield.
The triangular join method illustrated above works well only if the data is sufficiently partitioned that aggregates are performed over a few rows.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 13, 2011 at 10:09 am
Thank you both for your reply. I'm sorting through the examples now (kind of information overload, but I'm definitely learning a lot) to see how I can apply it to my specific situation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply