Aggregating Financial Data to 1 minute Intervals

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

General SQL Server Forums

New to SQL Server Programming

Aggregating Financial Data to 1 minute Intervals

Author

Topic

DJO
Starting Member

2 Posts

Posted - 2006-08-19 : 15:33:11

I'm trying to create a query to return Open, Close, Max and Min Price for each 1 minute interval. Source data has two fields - Price, and Datestamp at 5 second intervals.

I can calculate the Max and Min (below) and set the datestamp to the middle of the interval, but get stuck on how to also return the Open and Close price for each interval.

SELECT MAX(price) AS MaxPrice, MIN(price) AS MinPrice,
DATEADD(ss, 30, DATEADD(n,DATEDIFF n, '1/1/2006', DateStamp),'1/1/2006')) AS DateStamp
FROM MasterData
GROUP BY DATEDIFF(n, '1/1/2006',DateStamp)

Any ideas?

thanks in advance.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-19 : 20:44:46

You can use the F_START_OF_MINUTE function in this link to get the start of each minute to group by.

Start of Time Period Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

You can get the start and end prices with a self join on the min and max time within each one minute period.



select
	a.DateStampMinute,
	a.MaxPrice,
	a.MinPrice,
	StartPrice = b.Price
	EndPrice = c.Price
from
	(
	select
		DateStampMinute = dbo.F_START_OF_MINUTE(aa.DateStamp ),
		MaxPrice = max(aa.,Price),
		MinPrice = min(aa.Price),
		MaxDateStamp = max(aa.DateStamp),
		MinDateStamp = min(aa.DateStamp)
	from
		MasterData aa
	group by
		dbo.F_START_OF_MINUTE( aa.DateStamp )
	) a
	join
	MasterData b
	on a.MaxDateStamp = b.DateStamp
	join
	MasterData c
	on a.MaxDateStamp = c.DateStamp
order by
	a.DateStampMinute

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources