searching net interest income - 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)

searching net interest income

Author

Topic

rezasyah
Starting Member

12 Posts

Posted - 2006-07-12 : 23:01:20

i have a problem in searching net interest income.
if there is a table named FACT

FACT
-----------------------------------------------------
DATE | ACCOUNT_CODE | AMOUNT |
-----------------------------------------------------
2/1/2006 | Interest Income | 1000 |
2/1/2006 | Expense | 500 |
2/1/2006 | Interest Income | 5000 |
2/1/2006 | Interest Income | 2000 |
2/1/2006 | Expense | 1000 |
2/1/2006 | Expense | 1500 |
-----------------------------------------------------

the formula to calculate Net Interest Income is
Net Interest Income = Interest Income - Expense

i need a SELECT statement to get net interest income per day, without using a subquery. In this table FACT just one day example (2/1/2006), but i need per day SELECT statement

thanks for the answer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-13 : 00:18:21

[code]select DATE,
sum(case when ACCOUNT_CODE = 'Interest Income' then AMOUNT else 0 end) -
sum(case when ACCOUNT_CODE = 'Expense' then AMOUNT else 0 end) as [Net Interest Income]
from FACT
group by DATE[/code]

rezasyah
Starting Member

12 Posts

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

sorry, but when i try that statement in Access, it causes an error message :
syntax error (missing operator) in query expression 'sum(case when ACCOUNT_CODE = 'Interest Income' then AMOUNT else 0 end) -
sum(case when ACCOUNT_CODE = 'Expense' then AMOUNT else 0 end) as [Net Interest Income]'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-13 : 01:21:45

In Access you need to use IIF

select	DATE,
	sum(IIF (ACCOUNT_CODE = 'Interest Income' ,AMOUNT ,0) -
	sum(IIF (ACCOUNT_CODE = 'Expense' , AMOUNT ,0 ) as [Net Interest Income]
from	FACT
group by DATE

Madhivanan

Failing to plan is Planning to fail

rezasyah
Starting Member

12 Posts

Posted - 2006-07-13 : 02:28:22

thanks for all your replies,im very happy man now...