I have stock data in 1 min intervals and would like to convert it into other timeframes (e.g., 10 min, daily, monthly).
Here's is some sample data and my final goal:
[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.87 3.87 10
10-Feb-05 12:11:00 3.87 3.87 3.87 3.87 2
10-Feb-05 12:12:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:13:00 3.85 3.87 3.84 3.85 23
10-Feb-05 12:14:00 3.85 3.85 3.85 3.85 6
10-Feb-05 12:15:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:16:00 3.85 3.85 3.85 3.85 1
10-Feb-05 12:18:00 3.85 3.85 3.85 3.85 3
10-Feb-05 12:19:00 3.85 3.85 3.85 3.85 3
[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.84 3.85 50 *
*sum
Assuming your intervals will never span midnight,
you can do this by joining to an intervals table. This
has all minutes in one day with any intervals you want
and can be set up like this
CREATE TABLE MinuteIntervals(Mins INT NOT NULL PRIMARY KEY,
HMS CHAR(8) NOT NULL,
Interval10Mins INT NOT NULL,
Interval1Hour INT NOT NULL)
DECLARE @.i INT
SET @.i=0
WHILE @.i < 24*60
BEGIN
INSERT INTO MinuteIntervals(Mins,HMS,Interval10Mins,Interval1Hour)
SELECT @.i, CONVERT(CHAR(8),DATEADD(minute,@.i,'19000101'),108),
@.i/10, @.i/60
SET @.i=@.i+1
END
Now you need to join to this to your stock table
grouping by whatever interval you want.
SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
INNER JOIN MinuteIntervals m ON m.HMS=CONVERT(CHAR(8),t.[DateTime],108)
GROUP BY m.Interval10Mins
|||
Correction, the group by should read
GROUP BY m.Interval10Mins,CONVERT(CHAR(8),t.[DateTime],112)
|||
Of course this will only work for intervals less that one day.
Should have read the question fully!
For daily or longer intervals, don't join
to the MinuteIntervals table, simply group by the relevant amount
e.g. for daily
SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Close]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
GROUP BY CONVERT(CHAR(8),t.[DateTime],112)
For monthly
GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),6)
For yearly
GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),4)
I very much appreciate your help. If I understand the roles of tables t and t2, it appears the code only works if two different tables are used.
Does that mean I have to first create a copy of the original table in order to run the script correctly?
|||Those are not tables they are just aliases for tables. Since you are joining with the same table you need to use table aliases to make the names unique for referencing columns in the query. See the SELECT statement topic in Books Online for the syntax details.|||Yes, you are so right 
. That's what I get for trying to cut back on my caffeine.
It works like a charm 
. Thanks to all who have helped. I really do appreciate it.
