Query Open/High/Low/Close Tick Price data from SQL Server

I got a call from one of my prop trader customers today;  They wanted to pull back the tick prices in 5 and 10 minute bars, showing the OHLC in each interval. That means returning the Open, High, Low and Close prices for each 'bar'. Time is the only consideration for this chart; volume and trading activity have no bearing. In periods of high market activity, there would be more bars than in periods of low market activity. This type of collection allows traders to analyze price movement and market volatility.  

This is an example of the logic I built for them, trimmed down only for AAPL. I have coded it for any interval they choose;  5 and 10 minute intervals, or 1, 2 or 3 minutes, etc. See here:

     -- create and load @tickdata

    DECLARE @TickData AS Table ( 

TimeStamp DATETIME,

Price MONEY,

Symbol VARCHAR(5)

    )

    INSERT @TickData VALUES 

('2015-06-09 09:15:01',22.11,'AAPL'),

('2015-06-09 09:15:02',22.10,'AAPL'),

('2015-06-09 09:15:04',22.25,'AAPL'),

('2015-06-09 09:15:58',22.45,'AAPL'), 

('2015-06-09 09:16:03',23.12,'AAPL'), 

('2015-06-09 09:16:08',23.15,'AAPL'), 

('2015-06-09 09:17:10',22.28,'AAPL'), 

('2015-06-09 09:17:30',22.13,'AAPL'), 

('2015-06-09 09:18:59',22.15,'AAPL'),

('2015-06-09 09:18:02',20.19,'AAPL'), 

('2015-06-09 09:18:04',22.25,'AAPL'), 

('2015-06-09 09:20:58',24.14,'AAPL'), 

('2015-06-09 09:20:03',22.22,'AAPL'), 

('2015-06-09 09:21:08',22.28,'AAPL'), 

('2015-06-09 09:24:10',22.29,'AAPL'), 

('2015-06-09 09:25:30',22.13,'AAPL'), 

('2015-06-09 09:27:37',22.10,'AAPL'), 

('2015-06-09 09:27:40',23.25,'AAPL'), 

('2015-06-09 09:28:43',22.45,'AAPL'), 

('2015-06-09 09:28:50',22.22,'AAPL'), 

('2015-06-09 09:28:52',23.25,'AAPL'), 

('2015-06-09 09:29:53',22.28,'AAPL'), 

('2015-06-09 09:30:54',20.13,'AAPL'), 

('2015-06-09 09:30:56',21.11,'AAPL'), 

('2015-06-09 09:30:59',22.25,'AAPL'), 

('2015-06-09 09:31:02',22.45,'AAPL'), 

('2015-06-09 09:31:03',22.22,'AAPL'), 

('2015-06-09 09:32:07',22.25,'AAPL'), 

('2015-06-09 09:32:11',22.28,'AAPL'), 

('2015-06-09 09:34:12',22.13,'AAPL'), 

('2015-06-09 09:35:15',22.10,'AAPL'),

('2015-06-09 09:35:16',22.25,'AAPL'), 

('2015-06-09 09:36:18',22.45,'AAPL'), 

('2015-06-09 09:37:20',22.22,'AAPL'), 

('2015-06-09 09:37:25',22.25,'AAPL'), 

('2015-06-09 09:40:10',22.28,'AAPL'), 

('2015-06-09 09:40:11',22.13,'AAPL'); 

     -- pull it back out at intervals for the given @TimeInterval

     DECLARE @TimeInterval AS INT

     SET @TimeInterval= 10 -- minutes, 1 = 1 min, 5 = 5 min, etc.

     SELECT

Symbol,

DATEADD(mi,DATEPART(hh,TimeStamp)*60+    
           (DATEPART(mi,TimeStamp)/@TimeInterval) * @TimeInterval, CONVERT(Datetime,             CONVERT(varchar,TimeStamp,102))) [Time],

  (SELECT PRICE FROM @TickData WHERE TimeStamp = MIN(t.TimeStamp)) [Open],

MAX(Price) [High],

MIN(Price) [Low],

(SELECT PRICE FROM @TickData WHERE TimeStamp = MAX(t.TimeStamp)) [Close]

     FROM 

@TickData t

     GROUP BY 

Symbol, 

DATEADD(mi,DATEPART(hh,TimeStamp)*60+    
           (DATEPART(mi,TimeStamp)/@TimeInterval) * @TimeInterval, CONVERT(Datetime,             CONVERT(varchar,TimeStamp,102)));

I've run it for 2, 5 and 10 minute bars, and posted the output below for each. Take a look at that 2 minute bar; there are two spots with a larger than 2 minute jump. This is because it's coded to bring data back only for the bars where price data exists. Try it out on your own data, let me know what you think.

   2 minute bars -  

  

  5 minute bars -   

10 minute bars -