Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-07-11 : 21:30:20

Hi there

I got this following table:

FinancialMonth, FinancialYear, BudgetAmount
1, 2005, 10
2, 2005, 15
3, 2005, 20
4, 2005, 25
5, 2005, 30
6, 2005, 35
7, 2005, 40
8, 2005, 45
9, 2005, 50
10, 2005, 55
11, 2005, 60
12, 2005, 65
1, 2006, 5
2, 2006, 10

I want to add another column that called YTDBuddgetAmount which have the following condition:
IF FinancialMonth = 1 then get the SUM of BudgetAmount OF FinancialMonth IN (1) FOR SPECIFIC YEAR
IF FinancialMonth = 2 then get the SUM of BudgetAMount OF FinancialMonth IN (1, 2) FOR SPECIFIC YEAR

So basically, it will have like this:

FinancialMonth, FinancialYear, BudgetAmount, YTDBUdgetAmount
1, 2005, 10, 10
2, 2005, 15, 25
3, 2005, 20, 45
...
1, 2006, 5, 5
2, 2006, 10, 15

I'm trying to use case and no luck.

I'm appreciated your help.

Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-11 : 21:58:48

Simple grouping should do it:

select FinancialYear, FinancialMonth, (select sum(BudgetAmount) from table1 where FinancialYear=a.FinancialYear and FinancialMonth<=a.FinancialMonth)
from table1 a

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-11 : 23:34:45

Why don't you perform the cumulative function in your front end application ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-12 : 02:12:11

Where do you want to show data?
If you use Reports, then make use of Running Total feature

Madhivanan

Failing to plan is Planning to fail

dewacorp.alliances

452 Posts

Posted - 2006-07-13 : 02:32:00

quote:

Originally posted by timmy

Simple grouping should do it:

select FinancialYear, FinancialMonth, (select sum(BudgetAmount) from table1 where FinancialYear=a.FinancialYear and FinancialMonth<=a.FinancialMonth)
from table1 a

Thanks for that. Now I got another issue which is FinanceMonth has an extra value which is 0 and 13. The business rule is ignore 0. For 13, make it as null as well but add BudgetAmount of 13 into FinancialMonth 12.

I've done the modification of your quiry by adding and FinanceMonth >= 1 to cater the 0 thingy but not sure how to handle the FinanceMonth - 13 one.

select FinancialYear,
FinanceMonth,
BudgetAmount,
(select sum(BudgetAmount)
from test b
where b.FinancialYear = a.FinancialYear and b.FinanceMonth <= a.FinanceMonth
and FinanceMonth >= 1)
AS YTDBudgetAmount
from test a
ORDER BY FinancialYear,
FInanceMonth

Thanks

dewacorp.alliances

452 Posts

Posted - 2006-07-13 : 02:34:04

quote:
Originally posted by madhivanan

Where do you want to show data?
If you use Reports, then make use of Running Total feature

Madhivanan

Failing to plan is Planning to fail

It needs to be done in table level which is the query it's all about due the app is lack of feature that you mentioned: Running Total feature.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources