Determining price for discreet periods from a list of trades

Question

I have a discreet list of historic trades from a commodity exchange, with a datetime and a value e.g.

tradeTime                  |  price
-----------------------------------
2014-06-11T00:21:12+02:00  |  647.24
2014-06-11T00:23:12+02:00  |  700.18
2014-06-11T00:28:12+02:00  |  750.23
2014-06-11T00:40:00+02:00  |  767.81
2014-06-11T00:51:12+02:00  |  711.46

Now I want to be able to create a list of the price every hour. So the three logical possibilities are:

1. There was 1 trade in that period - great, that's the price (opening and closing)
2. There were multiple trades - the first is the opening price and the last is the closing
3. There were no trades - the opening & closing price should be the most recent trade prior to the period.

So with the above example, looking at 10min increments, the output should be something like:

period         |  open    | close
--------------------------------
00:20 -> 00:30 |  648.24  | 750.23
00:30 -> 00:40 |  750.23  | 767.81
00:40 -> 00:50 |  767.81  | 711.46

-

SELECT * FROM sales BETWEEN From_date AND To_date

possibly with a MIN(), MAX() for the dates to select the price? I am not sure how this would all come together with the

Answer 1

Try this query:

WITH qq AS (
  SELECT cast('2014-06-11T00:00:00' as datetime) as startTime,
         dateadd( minute, 10, cast('2014-06-11T00:00:00' as datetime)) as endTime
  UNION ALL
  SELECT endTime, dateadd( minute, 10, endTime )
  FROM qq
  WHERE endTime < cast('2014-06-12T00:00:00' as datetime) 
)
SELECT startTime, endTime,
       coalesce( opening, opening1 ) as opening,
       coalesce( closing, opening, opening1 ) as closing
FROM (
   SELECT qq.startTime, qq.endTime,
       (SELECT TOP 1 price FROM table1 t1 
        WHERE t1.tradeTime >= qq.startTime AND t1.tradeTime < qq.endTime
        ORDER BY t1.tradeTime ) As opening,
       (SELECT TOP 1 price FROM table1 t1 
        WHERE t1.tradeTime >= qq.startTime AND t1.tradeTime < qq.endTime
        ORDER BY t1.tradeTime DESC ) As closing,
       (SELECT TOP 1 price FROM table1 t1 
        WHERE t1.tradeTime < qq.startTime
        ORDER BY t1.tradeTime DESC ) as opening1
   FROM qq
) x
option (maxrecursion 0)

Demo: http://sqlfiddle.com/#!18/b9363/6

|            startTime |              endTime | opening | closing |
|----------------------|----------------------|---------|---------|
| 2014-06-11T00:00:00Z | 2014-06-11T00:10:00Z |  (null) |  (null) |
| 2014-06-11T00:10:00Z | 2014-06-11T00:20:00Z |  (null) |  (null) |
| 2014-06-11T00:20:00Z | 2014-06-11T00:30:00Z |  647.24 |  750.23 |
| 2014-06-11T00:30:00Z | 2014-06-11T00:40:00Z |  750.23 |  750.23 |
| 2014-06-11T00:40:00Z | 2014-06-11T00:50:00Z |  767.81 |  767.81 |
| 2014-06-11T00:50:00Z | 2014-06-11T01:00:00Z |  711.46 |  711.46 |
| 2014-06-11T01:00:00Z | 2014-06-11T01:10:00Z |  711.46 |  711.46 |
| 2014-06-11T01:10:00Z | 2014-06-11T01:20:00Z |  711.46 |  711.46 |
| 2014-06-11T01:20:00Z | 2014-06-11T01:30:00Z |  711.46 |  711.46 |
| 2014-06-11T01:30:00Z | 2014-06-11T01:40:00Z |  711.46 |  711.46 |
| 2014-06-11T01:40:00Z | 2014-06-11T01:50:00Z |  711.46 |  711.46 |
| 2014-06-11T01:50:00Z | 2014-06-11T02:00:00Z |  711.46 |  711.46 |
...
...
...