Candlestick chart query - 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 2008 Forums

Transact-SQL (2008)

Candlestick chart query

Author

Topic

robokop9
Starting Member

1 Post

Posted - 2012-01-05 : 21:49:38

Hi all

I'm trying to determine the best way construct a query/stored procedure for a candlestick chart I'm creating.

I have a database with a single table with about 1.2 million records called "historical_data" that contains the following columns:

id, date, price, volume

What I need to do is construct a query which will return the following values in 24 hr increments over a period of time, say three months.

date, open price, close price, high price, low price

What is the most efficient/best way to go about getting this done?

Thank you in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-05 : 23:31:42

can you post some sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-06 : 07:38:05

Assuming that date is not just the date, but the timestamp of the market price, you can write three separate (and simple) queries like this:

SELECT
	CAST([date] AS DATE) AS [Date],
	MIN(price),
	MAX(price)
FROM
	YourTable
GROUP BY
	CAST([date] AS DATE);

For opening price:

SELECT
	*	
FROM
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY CAST([date] AS DATE) ORDER BY [date] ASC ) AS OpenN
	FROM
		YourTable
)s
WHERE OpenN = 1
ORDER BY [date];

A similar one for closing price - change ASC to DESC in the order by clause on row_number.

The queries can be combined into one, but that would make it more complex and perhaps even less efficient.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources