searching variance (help me, again...)

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 variance (help me, again...)

Author

Topic

rezasyah
Starting Member

12 Posts

Posted - 2006-07-13 : 02:44:35

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 | Interest Income | 500 |
2/1/2006 | Interest Income | 5000 |
3/1/2006 | Interest Income | 2000 |
3/1/2006 | Interest Income | 1000 |
3/1/2006 | Interest Income | 1500 |
4/1/2006 | Interest Income | 2000 |
4/1/2006 | Interest Income | 1000 |
4/1/2006 | Interest Income | 1500 |
-----------------------------------------------------

the formula to calculate Variance between the current day's balance against the previous day's balance is
Variance = Interest Income of current date- Interest Income of previous date
i need a SELECT statement to get variance for each day, if there is no data on the previous day, we can assume it 0.

please help me once again, thank you very much

dewacorp.alliances

452 Posts

Posted - 2006-07-13 : 02:59:07

I'm trying to understand this.

You mentioned that:
Variance = Interest Income of current date- Interest Income of previous date

But for the interest income for each, they have 3 values the same day. How to handle this then? An average of this 3 values against the previous average of interest income or ????

Thanks

rezasyah
Starting Member

12 Posts

Posted - 2006-07-13 : 03:08:21

nope, we must compute sum of interest income on the same day, and then we substract it with sum of interest income on the previous day. Sum, not average...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-13 : 03:43:57

[code]
declare @FACT table
(
DATE datetime,
ACCOUNT_CODE varchar(30),
AMOUNT int
)
set dateformat dmy
insert into @FACT
select '2/1/2006' , 'Interest Income' , 1000 union all
select '2/1/2006' , 'Interest Income' , 500 union all
select '2/1/2006' , 'Interest Income' , 5000 union all
select '3/1/2006' , 'Interest Income' , 2000 union all
select '3/1/2006' , 'Interest Income' , 1000 union all
select '3/1/2006' , 'Interest Income' , 1500 union all
select '4/1/2006' , 'Interest Income' , 2000 union all
select '4/1/2006' , 'Interest Income' , 1000 union all
select '4/1/2006' , 'Interest Income' , 1500

select a.DATE, a.ACCOUNT_CODE, isnull(a.TODAY_AMOUNT - b.YESTERDAY_AMOUNT, 0) as VARIANCE
from
(
select DATE, ACCOUNT_CODE, sum(AMOUNT) as TODAY_AMOUNT
from @FACT
group by DATE, ACCOUNT_CODE
) a
left join
(
select DATE, ACCOUNT_CODE, sum(AMOUNT) as YESTERDAY_AMOUNT
from @FACT
group by DATE, ACCOUNT_CODE
) b
on a.ACCOUNT_CODE = b.ACCOUNT_CODE
and a.DATE = dateadd(day, 1, b.DATE)[/code]

RESULT
DATE ACCOUNT_CODE VARIANCE
----------- ------------------------------ -----------
2006-01-02 Interest Income 0
2006-01-03 Interest Income -2000
2006-01-04 Interest Income 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-13 : 03:53:55

oh just saw on your other thread you are using access...
The earlie one i post might not work on Access. try this . ..


select	DATE, ACCOUNT_CODE, 
	isnull(SUM(AMOUNT) -
	(select sum(AMOUNT) from @FACT x where x.ACCOUNT_CODE = f.ACCOUNT_CODE and x.DATE = dateadd(day, -1, f.DATE)), 0)
from	@FACT f
group by DATE, ACCOUNT_CODE