PLEASE HELP NEEDED? - with a select avg
-
no problem. definitely when i get the solution from my boss than i will post it here.
thank u all once again!
-
Now, one thing still puzzles me.
You have to make sure how you will deal with the last day of your search range. Will it get included or not? The solution provided does not take this into account
Consider the state 130. There are two entries for that state:
select 130, '3.3.2005', 170
select 130, '12.3.2005 ', 200
And we are given the range from 20050203 - 20050315.
So, if you need to inlcude that last day, you have to consider 9 days (20050303-20050311) with a balance of 170 + 4 days with a balance of 200 and then divide by 13 which will yield 179,2307692 instead of 177,5.
From my own everyday experience, make sure you check this thing very carefully with your internal and regulatory guidelines. Almost every banking and accounting framework has strict rules on how to calculate accrued interest, amortisations or things like that.
Good luck!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
ok well this is a solution that i did with my boss.......
: 
declare @startdate datetime
declare @enddate datetime
declare @total numeric (19,2)
declare @state numeric (19,2)
declare @date datetime
set @startdate = 'feb 5, 2005'
set @enddate = 'mar 15, 2005'
set @total = 0
set @date = @startdate
while @date <= @enddate
begin
select @state = state from mytable
where date = (
select max(date) from mytable where date <= @date)
set @total = @total + @state
set @date = @date + 1
end
/*print @total*/
print @total/datediff(day, @startdate, @enddate)+1
thanx for your help........
-
Do you want to tell your boss, that this solution is not really set-oriented?
After surviving yesterday's obvious brain death, can't resist to post one solution with the help of a calendar table.
Given this DDL:
CREATE TABLE Calendar
(
caldate DATETIME
)
DECLARE @i INT
SET @i = 1
WHILE @i <= 60
BEGIN
INSERT INTO Calendar VALUES(DATEADD(d,@i,'20050201'))
SET @i = @i + 1
END
CREATE TABLE foobar
(
state VARCHAR(20)
, CloseDate DATETIME
, CloseBal DECIMAL(9,2)
)
set dateformat dmy
insert foobar
select 110, '2.2.2005', 120
union all select 150, '3.2.2003', 130
union all select 120, '4.2.2005', 140
union all select 160, '17.2.2005', 150
union all select 140, '24.2.2005', 160
union all select 130, '3.3.2005', 170
union all select 140, '4.3.2005', 180
union all select 150, '8.3.2005', 190
union all select 130, '12.3.2005 ', 200
union all select 160, '18.3.2005', 210
union all select 170, '27.3.2005', 220
--drop table foobar,calendar
you can do
select
fool.state
, sum(fool.running)/sum(fool.duration) avg_balance
from
(select
min(f.state) state
, cast(isnull(min(f1.closedate)
, max(c.caldate))-case
when min(f.closedate) <'20050203'
then '20050203'
else min(f.closedate) end as int) duration
, avg(f.closebal)*cast(isnull(min(f1.closedate)
, max(c.caldate))-case
when min(f.closedate) <'20050203'
then '20050203'
else min(f.closedate) end as int) running
from calendar c
left join foobar f
on c.caldate>f.closedate
left join foobar f1
on f.state=f1.state and f1.closedate>f.closedate
where c.caldate >='20050203' and c.caldate<='20050315'
group by f.state, f.closebal, f.closedate
 
foolgroup by fool.state
state avg_balance
-------------------- ----------------------------------------
110 120.000000
120 140.000000
130 177.500000
140 171.578947
150 140.500000
160 150.000000
(6 row(s) affected)
It actually catches also an error in the other solution. Consider:
union all select 150, '3.2.2003', 130
union all select 150, '8.3.2005', 190
Obviously this state start on 20050203 with a balance of 130
Now, from 20050203 till 20050308 there are 33 days with this balance and from 20050308 till 20050315 7 days with a balance of 190. This averages to 140,5 not 162,307692.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I think this can be done in 1 SQL statement. I suggest you build the statement bit by bit, until you have what you need.
The first stage is probably to get the state and the date ranges for that state in 1 result set, such as below:
select
m1.state
,m1.date
,(select min(m2.date)
from bank_data m2
where m2.date > m1.date
and m1.account_number = m2.account_number) as d2
from bank_data m1
order by m1.date desc
You can then extract some data from this:
select
m3.state
,m3.date
,datediff(dd,m3.date,m3.d2)
from (select
m1.state
,m1.date
,(select min(m2.date)
from bank_data m2
where m2.date > m1.date
and m1.account_number = m2.account_number) as d2
from bank_data m1) m3
order by m3.date desc
I'll let you work out the rest, but you should now see you have the basis for your answer. In one result set you have the start state and the number of days for which it was valid. You have to add extra code to define the start and end for when you want the data, then work out the average balance between these dates.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Try this... I'll post it along with DDL. I included in the query several columns that are not necessary, but could help you understand how it works. Also, I'm sure you'll have to tweak it (especially the date comparison - I don't know which value is valid on the day of transaction : old or new one? etc.).
create table #account(state int, trandate datetime)
insert into #account(state, trandate) values (110, '2005.02.02')
insert into #account(state, trandate) values (150, '2005.02.03')
insert into #account(state, trandate) values (120, '2005.02.04')
insert into #account(state, trandate) values (160, '2005.02.17')
insert into #account(state, trandate) values (140, '2005.02.24')
insert into #account(state, trandate) values (130, '2005.03.03')
insert into #account(state, trandate) values (140, '2005.03.04')
insert into #account(state, trandate) values (150, '2005.03.08')
insert into #account(state, trandate) values (130, '2005.03.12')
insert into #account(state, trandate) values (160, '2005.03.18')
insert into #account(state, trandate) values (170, '2005.03.27')
DECLARE @startdate datetime, @enddate datetime
SELECT @startdate = '2005.02.02', @enddate = '2005.03.15'/*parameters for calculation*/
IF @startdate < (select min(trandate) from #account) OR @enddate > (select max(trandate) from #account) PRINT 'ERROR - invalid period'
ELSE
BEGIN
SELECT
SUM(Q.state * Q.period) AS sum_of_daily_states,
SUM(Q.period) AS period_days,
SUM(Q.state * Q.period)/ SUM(Q.period) AS average_state
FROM
(select state, trandate, (select isnull(min(trandate), @enddate) from #account where trandate > a.trandate and trandate <=@enddate) as enddate,
datediff(d, trandate,(select isnull(min(trandate), @enddate) from #account where trandate > a.trandate and trandate <=@enddate)) as period
from #account a) as Q
WHERE Q.trandate BETWEEN @startdate AND @enddate
END
-
thanx all for the scripts.......i think it was just an excercise......cause i just have to learn ms sql but probaly wont be using it, cause i'm working in the field of data mining (started to work).... but thanx all for puting your answers here and helping me understand more and ofcourse learn ms sql.....
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply