searching average balance - 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 average balance

Author

Topic

rezasyah
Starting Member

12 Posts

Posted - 2006-07-13 : 21:54:53

a FACT table contains these sample data :

FACT
--------------------------------
DATE | ACCOUNT_NO | AMOUNT | END OF MONTH
--------------------------------
2/1/2006 | 080001 | 1000 | N
2/1/2006 | 080002 | 500 | N
2/1/2006 | 080003 | 5000 | N
3/1/2006 | 080001 | 2000 | N
3/1/2006 | 080002 | 1000 | N
3/1/2006 | 080003 | 1500 | N
4/1/2006 | 080001 | 2000 | N
4/1/2006 | 080002 | 1000 | N
4/1/2006 | 080003 | 1500 | N
... | ... | ... |
31/1/2006| 080001 | 7000 | Y
31/1/2006| 080002 | 5000 | Y
31/1/2006| 080003 | 8000 | Y
--------------------------------

i need a select statement to get this result :

--------------------------------------------------------
MONTH_YEAR | ACCOUNT_NO | BALANCE_TYPE | AMOUNT |
--------------------------------------------------------
JAN 2006 | 080001 |AVERAGE BALANCE | 5000 |
JAN 2006 | 080002 |AVERAGE BALANCE | 3500 |
JAN 2006 | 080003 |AVERAGE BALANCE | 4500 |
JAN 2006 | 080001 |END OF MONTH BALANCE | 7000 |
JAN 2006 | 080002 |END OF MONTH BALANCE | 5000 |
JAN 2006 | 080003 |END OF MONTH BALANCE | 8000 |
--------------------------------------------------------

the formula to calculate Average Balance and End of Month Balance are :
- Average Balance is the sum of all the balances per month divided
by the number of days of the month
- End of Month Balance is the balance on the last day of the month

thanks before for all replies

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-13 : 22:12:23

provide your query and tell us what error you encountered why it didn't work

--------------------
keeping it simple...

rezasyah
Starting Member

12 Posts

Posted - 2006-07-13 : 22:15:39

I have no idea at all to get the expected result... this is a test question actually, please help me..

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-13 : 22:47:00

Most people here won't answer test/homework questions, especially for people who are unwilling to even make an attempt to do it themselves.

CODO ERGO SUM

rezasyah
Starting Member

12 Posts

Posted - 2006-07-13 : 23:04:22

okay, i've tried this statement but it doesnt match the expected result

SELECT a.ACCOUNT_NO, a.AVERAGE, b.[END OF MONTH]
FROM
(
select ACCOUNT_NO, avg(AMOUNT) as [AVERAGE]
from FACT2
group by ACCOUNT_NO
)
a
left join
(
select ACCOUNT_NO, AMOUNT as [END OF MONTH]
from FACT2
where END_OF_MONTH = 'Y'
)
b
on a.ACCOUNT_NO=b.ACCOUNT_NO

thanks for the 'advice' from Michael

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-13 : 23:46:57

computation for average is not correct (imagine there can be more than 2 transactions a day for each account) and instead of join, a union all will do

to get you started...


declare @FACT table(dt datetime,account_no varchar(6),amount int,eom varchar(1))

insert into @fact
select '1/2/2006','080001','1000','N' union select
'1/2/2006','080002','500','N'  union select
'1/2/2006','080003','5000','N' union select
'1/3/2006','080001','2000','N' union select
'1/3/2006','080002','1000','N' union select
'1/3/2006','080003','1500','N' union select
'1/4/2006','080001','2000','N' union select
'1/4/2006','080002','1000','N' union select
'1/4/2006','080003','1500','N' union select
'1/31/2006','080001','7000','Y' union select
'1/31/2006','080002','5000','Y' union select
'1/31/2006','080003','8000','Y'


select (datename(month,dt) +  ' ' + convert(varchar(4),datepart(year,dt))) as month_year,'end of month balance' as balance_type,
account_no,sum(amount)/31 as amount 
from @fact
group by account_no,eom,datename(month,dt) +  ' ' + convert(varchar(4),datepart(year,dt))
having eom='N'
union all
select (datename(month,dt) +  ' ' + convert(varchar(4),datepart(year,dt))) as month_year,'end of month balance' as balance_type,
account_no,amount
from @fact where eom='Y'

--edit
or did you mean sum(amount)/count(*) ?

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources