OHLC-Stockmarket Group Multiple Timeframe Data with T-SQL

Question

I am using SQL Server 2008 R2 and you need to create new tables grouped by time intervals.

Data is data from the stock market index. I have data with an interval of 1 minute, now I need them in 5,10,15,30,45,60 ... minute intervals. My primary key is a timestamp.

My question is: how to query a data table in 1 minute to return data grouped by a specific time interval, for example, 5 minute intervals.

The query should return the highest, lowest, last and first values ​​in this particular group and, most importantly, also the last timestamp entry in the group.

I am very new to SQL and have tried many codes found on the net, but I can’t exactly return the desired results.

Data:

TimeStamp | Open | High | Low | Close 2012-02-17 15:15:0 | 102 | 110 |100 |105 2012-02-17 15:16:0 |106 |112 |105 |107 2012-02-17 15:17:0 | 106 |110 |98 |105 2012-02-17 15:18:0 |105 |109 |104 |106 2012-02-17 15:19:0 |107 |112 |107 |112 2012-02-17 15:20:0 |115 |125 |115 |124 

Desired query result (5 minutes):

 Timestamp |Open|High|Low|Close 2012-02-15:19:0 |102 |125 |98 |124 2012-02-15:24:0 |115.|....|...|... 2012-02-15:29:0 |....|....|...|... 

Answer 1

When you convert datetime to float , you get a few days. If you multiply the value by 24 * 12 , you get several intervals of 5 minutes. Therefore, if you group:

 cast(cast(timestamp as float) * 24 * 12 as int) 

you can do aggregates in five minutes:

 select min(timestamp) , max(high) as Highest , min(low) as Lowest from @t group by cast(cast(timestamp as float) * 24 * 12 as int) 

Finding the first and last row is difficult in SQL Server. Here is one way: row_number :

 select min(timestamp) , max(high) as Highest , min(low) as Lowest , min(case when rn_asc = 1 then [open] end) as first , min(case when rn_desc = 1 then [close] end) as Last from ( select row_number() over ( partition by cast(cast(timestamp as float) * 24 * 12 as int) order by timestamp) as rn_asc , row_number() over ( partition by cast(cast(timestamp as float) * 24 * 12 as int) order by timestamp desc) as rn_desc , * from @t ) as SubQueryAlias group by cast(cast(timestamp as float) * 24 * 12 as int) 

Here is a working example in SE data.