CREATE PROCEDURE [IVolatilityUser].[CalcMovAvg_date]

AS

declare @date as smalldatetime
declare @stock_id as int
declare @avg90 as real
declare @avg180 as real
declare @avg60 as real
declare @avg30 as real

declare vol_cursor2 cursor
for 
select stock_id, t_date
from dbo.RealizedVolatilities
where t_date between '2-1-2006' and '2-10-2006'

open vol_cursor2

fetch vol_cursor2 into @stock_id, @date

if (@@FETCH_STATUS = 2)
begin
    print 'No rows fetched'
    close vol_cursor2
    return
end 

while (@@FETCH_STATUS = 0)

begin
    select @avg90 =  avg(b.vol_90)
    from dbo.RealizedVolatilities a , dbo.RealizedVolatilities b
    where a.stock_id = b.stock_id
    and b.t_date in (select top 90 c.t_date 
                    from dbo.RealizedVolatilities c
                    where c.t_date <= @date
		    and c.stock_id  = @stock_id
		    order by c.t_date desc)
    and a.stock_id = @stock_id
    and a.t_date = @date
    group by a.stock_id, a.t_date
    print @date
    fetch vol_cursor2 into @stock_id,@date

select @avg180 = avg(b.vol_180)
    from dbo.RealizedVolatilities a , dbo.RealizedVolatilities b
    where a.stock_id = b.stock_id
    and b.t_date in (select top 180 c.t_date 
                    from dbo.RealizedVolatilities c
                    where c.t_date <= @date
		    and c.stock_id  = @stock_id
		    order by c.t_date desc)
    and a.stock_id = @stock_id
    and a.t_date = @date
    group by a.stock_id, a.t_date

select @avg60 =  avg(b.vol_60)
    from dbo.RealizedVolatilities a , dbo.RealizedVolatilities b
    where a.stock_id = b.stock_id
    and b.t_date in (select top 60 c.t_date 
                    from dbo.RealizedVolatilities c
                    where c.t_date <= @date
		    and c.stock_id  = @stock_id
		    order by c.t_date desc)
    and a.stock_id = @stock_id
    and a.t_date = @date
    group by a.stock_id, a.t_date


select @avg30 = avg(b.vol_30)
    from dbo.RealizedVolatilities a , dbo.RealizedVolatilities b
    where a.stock_id = b.stock_id
    and b.t_date in (select top 30 c.t_date 
                    from dbo.RealizedVolatilities c
                    where c.t_date <= @date
		    and c.stock_id  = @stock_id
		    order by c.t_date desc)
    and a.stock_id = @stock_id
    and a.t_date = @date
    group by a.stock_id, a.t_date


update dbo.ImpVols set avg_180 = @avg180 where imp_stock_id = @stock_id and imp_t_date = @date
end
deallocate vol_cursor2
close vol_cursor2
GO

update	ImpVols
set	avg_180 = MovingAverages.avg_180
from	ImpVols
	inner join --MovingAverages
		(select	CurrentData.stock_id,
			CurrentData.t_date,
			avg(PriorData.vol_180) as avg_180
		from	RealizedVolatilities CurrentData
			inner join RealizedVolatilities PriorData
				on CurrentData.stock_id = PriorData.stock_id
				and PriorData.t_date <= CurrentData.t_date
				and PriorData.t_date > dateadd(day, -180, CurrentData.t_date)) MovingAverages
		on ImpVols.imp_stock_id = MovingAverages.stock_id
		and MovingAverages.t_date <= ImpVols.imp_t_date
		and MovingAverages.t_date > dateadd(day, -180, ImpVols.imp_t_date)

update	ImpVols
set	avg_180 = MovingAverages.avg_180
from	ImpVols
	inner join --MovingAverages
		(select	CurrentData.stock_id,
			CurrentData.t_date,
			avg(PriorData.vol_180) as avg_180
		from	RealizedVolatilities CurrentData
			inner join RealizedVolatilities PriorData
				on CurrentData.stock_id = PriorData.stock_id
				and PriorData.t_date <= CurrentData.t_date
				and PriorData.t_date > dateadd(day, -180, CurrentData.t_date)
		group by CurrentData.stock_id,
			CurrentData.t_date) MovingAverages
		on ImpVols.imp_stock_id = MovingAverages.stock_id
		and MovingAverages.t_date <= ImpVols.imp_t_date
		and MovingAverages.t_date > dateadd(day, -180, ImpVols.imp_t_date)

quote:

---

Originally posted by tm1177

i think there is a problem with the dateadd(day,-30,xxx) returns a date value that is 30 calendar days less than the current date. What i need is the average of the 30 days' worth of data, which runs more than 30 calender days, since there are only 5 trading days per week. This was the reason I was using the nested (select top30...), to retrieve the 30 most recent vols.

Is there another way to do this with specifying calender days?

---

declare @start_date	datetime
declare @end_date	datetime
select @end_date	= getdate()-1
select @start_date	= @end_date-100

select	top 30
	Weekdays = [DATE]
from 
	dbo.F_TABLE_DATE ( @start_date,@end_date)
where
	[DAY_OF_WEEK]	 between 2 and 6
order by
	[Date] desc

quote:

---

Originally posted by spirit1

so why is this called a theta join??

---

update	ImpVols
set	avg_180 = MovingAverages.avg_180
from	ImpVols
	inner join --MovingAverages
		(select	CurrentData.stock_id,
			CurrentData.t_date,
			avg(PriorData.vol_180) as avg_180
		from	RealizedVolatilities CurrentData
			inner join RealizedVolatilities PriorData
				on CurrentData.stock_id = PriorData.stock_id
		where	PriorData.t_date in
				(select	top 180 t_date
				from	RealizedVolatilities DataFilter
				where	DataFilter.stock_id = CurrentData.stock_id
					and DataFilter.t_date <= CurrentData.t_date)
		group by CurrentData.stock_id,
			CurrentData.t_date) MovingAverages
		on ImpVols.imp_stock_id = MovingAverages.stock_id
		and MovingAverages.t_date <= ImpVols.imp_t_date
		and MovingAverages.t_date > dateadd(day, -180, ImpVols.imp_t_date)

quote:

---

Originally posted by spirit1

so why is this called a theta join??

Go with the flow & have fun! Else fight the flow

---