Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Средний курс месяца до даты  [new]
kest007
Member

Откуда:
Сообщений: 6
Есть таблица с курсами валют
Причем в таблице "хранится не ежедневный курс валют, а лишь хронология смены курса"
[Валюта], [Курс], [Дата]

USD 30.1 '01.10.09'
USD 30.2 '02.10.09'
USD 30.3 '03.10.09'

USD 30.4 '06.10.09'
USD 30.5 '07.10.09'

Подскажите,
как рассчитать среднее значение курса на дату (в пределах месяца), с учетом предыдущих дат.
т.е.
на 01.10.09 30.1
на 02.10.09 (30.1+30.2)/2
на 03.10.09 (30.1+30.2+30.3)/3
на 04.10.09 (30.1+30.2+30.3+30.3)/4

Или хотя бы идею подскажите.

Заранее спасибо.
16 ноя 09, 16:01    [7934901]     Ответить | Цитировать Сообщить модератору
 Re: Средний курс месяца до даты  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
kest007
Или хотя бы идею подскажите.
JOIN с таблицей календаря
Если для какой-то даты календаря не нашлось курса в хронологии,
то найти ближайшую более раннюю дату по хронологии и взять курс из этой даты
16 ноя 09, 16:04    [7934924]     Ответить | Цитировать Сообщить модератору
 Re: Средний курс месяца до даты  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
kest007,

так не пойдёт ? (выводит средний курс по всем датам от начала месяца до введённой включительно)
declare @c table(cnc char(3),val numeric(12,4),dt datetime)
insert @c select 'usd',29.1,'20090928'
insert @c select 'usd',29.2,'20090929'
insert @c select 'usd',29.3,'20090930'
insert @c select 'usd',30.1,'20091001'
insert @c select 'usd',30.2,'20091002'
insert @c select 'usd',30.3,'20091003'
insert @c select 'usd',30.4,'20091004'
insert @c select 'usd',30.5,'20091005'
insert @c select 'usd',30.16,'20091016'
insert @c select 'usd',30.27,'20091027'
insert @c select 'usd',30.29,'20091029'

declare @dx datetime
set @dx=cast('20091020' as datetime)

select t.cnc,t.val,t.dt
      ,avg=sum(c2.val)/count(c2.cnc)
from(
  select *,d0=cast(convert(char(6),@dx,112)+'01' as datetime)
  from @c c1
  where dt between cast(convert(char(6),@dx,112)+'01' as datetime) and @dx
)t
left join @c c2 on c2.cnc=t.cnc and c2.dt between t.d0 and t.dt
group by t.cnc,t.val,t.dt
order by dt
result:
cncvaldtavg
usd30.10002009-10-01 00:00:00.00030.100000
usd30.20002009-10-02 00:00:00.00030.150000
usd30.30002009-10-03 00:00:00.00030.200000
usd30.40002009-10-04 00:00:00.00030.250000
usd30.50002009-10-05 00:00:00.00030.300000
usd30.16002009-10-16 00:00:00.00030.276666
16 ноя 09, 16:39    [7935222]     Ответить | Цитировать Сообщить модератору
 Re: Средний курс месяца до даты  [new]
kest007
Member

Откуда:
Сообщений: 6
Спасибо большое.
Похоже в комплексе два ответа = решение.
Проверю - отпишусь.
16 ноя 09, 16:57    [7935380]     Ответить | Цитировать Сообщить модератору
 Re: Средний курс месяца до даты  [new]
aleks2
Guest
declare @Table table([Валюта] char(3),[Курс] numeric(12,4),[Дата] datetime)
insert @Table select 'usd',29.1,'20090928'
insert @Table select 'usd',29.2,'20090929'
insert @Table select 'usd',29.3,'20090930'
insert @Table select 'usd',30.1,'20091001'
insert @Table select 'usd',30.2,'20091002'
insert @Table select 'usd',30.3,'20091003'
insert @Table select 'usd',30.4,'20091004'
insert @Table select 'usd',30.5,'20091005'
insert @Table select 'usd',30.16,'20091016'
insert @Table select 'usd',30.27,'20091027'
insert @Table select 'usd',30.29,'20091029'


declare @t table(id int identity primary key clustered, d datetime, v varchar(3), c numeric(12,4), unique (v, d))

declare @m datetime

set @m='20091001'


insert @t(d,v,c)
select [Дата], [Валюта], [Курс]
FROM
(
select @m as [Дата], [Валюта], [Курс]
FROM @Table T
WHERE
[Дата]=(select max([Дата]) FROM @Table TT WHERE TT.[Дата]<=@m)
UNION ALL
select [Дата], [Валюта], [Курс]
FROM @Table T
WHERE
@m<[Дата] AND [Дата]< dateadd(month, 1, @m)
UNION ALL
select dateadd(month, 1, @m) as [Дата], [Валюта], [Курс]
FROM @Table T
WHERE
[Дата]=(select max([Дата]) FROM @Table TT WHERE TT.[Дата]<dateadd(month, 1, @m))
) X
ORDER BY [Валюта], [Дата] ASC

select X.d, X.v, SUM(X.c*X.dT)/SUM(X.dT) avgC
FROM
(select v, d FROM @t) Y 
inner join
(select T.v, T.d, T.c, DATEDIFF(day,T.d,TT.d) dT
FROM @t T INNER JOIN @t tt
ON T.v=TT.v AND T.id+1=TT.id
) X
ON Y.v=X.v AND X.d<=Y.d
GROUP BY X.d, X.v
16 ноя 09, 17:08    [7935438]     Ответить | Цитировать Сообщить модератору
 Re: Средний курс месяца до даты  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
kest007,

если правильно понял задачу, то как то так:

declare @c table(cnc char(3),val numeric(12,4),dt datetime)
insert @c select 'usd',29.1,'20090901'
insert @c select 'usd',29.2,'20090929'
insert @c select 'usd',29.3,'20090930'
insert @c select 'usd',30.1,'20091001'
insert @c select 'usd',30.2,'20091002'
insert @c select 'usd',30.3,'20091003'
insert @c select 'usd',30.4,'20091004'
insert @c select 'usd',30.5,'20091005'
insert @c select 'usd',30.16,'20091016'
insert @c select 'usd',30.27,'20091027'
insert @c select 'usd',30.29,'20091029'


;WITH c AS (
	SELECT
		d + i-1 d
		,(SELECT TOP 1 val FROM @c c WHERE c.dt<=d + i-1 ORDER BY dt DESC) c
	FROM (SELECT row_number() OVER(ORDER BY name) i FROM sys.objects) a
		JOIN(
			SELECT
				cast(cast(year(dt) AS varchar) + RIGHT('0'+cast(month(dt) AS varchar),2) + '01' AS datetime) d
				,day(dateadd(m,1,cast(year(dt) AS varchar) + RIGHT('0'+cast(month(dt) AS varchar),2) + '01')-1) dc
			FROM @c GROUP BY month(dt),year(dt)
			) b ON a.i <= dc
	)
	
SELECT
	a.d
	,avg(b.c)
FROM c a
	JOIN c b ON b.d BETWEEN a.d - day(a.d)+1 AND a.d
GROUP BY a.d

16 ноя 09, 17:50    [7935722]     Ответить | Цитировать Сообщить модератору
 Re: Средний курс месяца до даты  [new]
kest007
Member

Откуда:
Сообщений: 6
Сергей Мишин
kest007,

если правильно понял задачу, то как то так:

declare @c table(cnc char(3),val numeric(12,4),dt datetime)
insert @c select 'usd',29.1,'20090901'
insert @c select 'usd',29.2,'20090929'
insert @c select 'usd',29.3,'20090930'
insert @c select 'usd',30.1,'20091001'
insert @c select 'usd',30.2,'20091002'
insert @c select 'usd',30.3,'20091003'
insert @c select 'usd',30.4,'20091004'
insert @c select 'usd',30.5,'20091005'
insert @c select 'usd',30.16,'20091016'
insert @c select 'usd',30.27,'20091027'
insert @c select 'usd',30.29,'20091029'


;WITH c AS (
	SELECT
		d + i-1 d
		,(SELECT TOP 1 val FROM @c c WHERE c.dt<=d + i-1 ORDER BY dt DESC) c
	FROM (SELECT row_number() OVER(ORDER BY name) i FROM sys.objects) a
		JOIN(
			SELECT
				cast(cast(year(dt) AS varchar) + RIGHT('0'+cast(month(dt) AS varchar),2) + '01' AS datetime) d
				,day(dateadd(m,1,cast(year(dt) AS varchar) + RIGHT('0'+cast(month(dt) AS varchar),2) + '01')-1) dc
			FROM @c GROUP BY month(dt),year(dt)
			) b ON a.i <= dc
	)
	
SELECT
	a.d
	,avg(b.c)
FROM c a
	JOIN c b ON b.d BETWEEN a.d - day(a.d)+1 AND a.d
GROUP BY a.d



Сергей, спасибо. Идеальное решение.

Всем большое спасибо за участие.
Тема закрыта.
16 ноя 09, 21:52    [7936534]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить