Sql Server: Get OHLC in one query with n recodrs

Question

I need get OHLC values from one query

SELECT [Open] -- first row value
  ,[High] -- max(High)
  ,[Low]  -- min(low)
  ,[Close]-- last row value
FROM [Forex] where Symbol ='EURUSD' and 
[timestamp] between '2013-07-18 01:00:00.000' and '2013-07-18 02:00:00.000'

and I want that query has 12 hours details. I mean I need to get 12 records with hourly OHLC values

Can any one help in the query. How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions? has implementation in MYSQL, I need in SQL server

Edit: Structure of the the table

CREATE TABLE [dbo].[Forex](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Symbol] [varchar](100) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[Bid] [decimal](18, 5) NOT NULL,
[Ask] [decimal](18, 5) NOT NULL,
[Open] [decimal](18, 5) NOT NULL,
[High] [decimal](18, 5) NOT NULL,
[Low] [decimal](18, 5) NOT NULL,
[NetChange] [decimal](18, 5) NOT NULL,
[PerChange] [decimal](18, 5) NOT NULL,
CONSTRAINT [PK_Forex] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and Index

CREATE NONCLUSTERED INDEX [index_Forex_29_85575343__K2_K1] ON [dbo].[Forex] 
(
[Symbol] ASC,
[Id] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

It's time to look for an upgrade. 2000 exited even *extended* support in April this year. There have been 4 releases since then and a fifth is currently in preview. — Damien_The_Unbeliever, Jul 19 '13 at 08:24

Ian Kenney · Accepted Answer · 2013-07-19T09:10:50.753

you can use something like

select [open],[min], [max], [close] 
from 
(
   select min(value) [min], max(value) [max] 
   from forex 
   where Symbol ='EURUSD' and 
   [timestamp] between '2013-07-18 01:00:00.000' and '2013-07-18 02:00:00.000'  
) minmax,

(
   select top 1 value as [open] 
   from forex 
   where Symbol ='EURUSD' and 
   [timestamp] between '2013-07-18 01:00:00.000' and '2013-07-18 02:00:00.000' 
   order by [timestamp] 
) fst,

(
  select top 1 value [close] 
  from forex 
  where Symbol ='EURUSD' and 
  [timestamp] between '2013-07-18 01:00:00.000' and '2013-07-18 02:00:00.000' 
  order by [timestamp] desc
) lst

see http://sqlfiddle.com/#!3/0547c/2

EDIT

Based on revised question... we can take the min and max id's grouped by time period (hour) together with the min and max values for that hour:

select 
   datepart(hh,timestamp) [hour], 
   min(value) [min], 
   max(value) [max],
   min(id) min_id, 
   max(id) max_id 
 from forex 
 where Symbol ='EURUSD' and 
[timestamp] between '2013-07-18 01:00:00.000' and '2013-07-18 13:00:00.000'
 group by datepart(hh,timestamp)

and join this with the forex table (twice) to get the open and close values.

Giving something like

   select minmax.[hour], f_min.value [open], [min], [max], f_max.value [close] 
    from 
    (select 
       datepart(hh,timestamp) [hour], 
       min(value) [min], 
       max(value) [max],
       min(id) min_id, 
       max(id) max_id 
     from forex 
     where Symbol ='EURUSD' and 
    [timestamp] between '2013-07-18 01:00:00.000' and '2013-07-18 13:00:00.000'
     group by datepart(hh,timestamp)
    ) minmax 
    join forex f_min on min_id = f_min.id
    join forex f_max on max_id = f_max.id

sqlfiddle

Read this http://msdn.microsoft.com/en-us/library/aa258265(v=sql.80).aspx it show the datepart function which is used in the query — Ian Kenney, Jul 19 '13 at 09:24

score 0 · Answer 2 · answered Jul 19 '13 at 09:24

This helped me

select minmax.[hour], f_min.[Open] [open], [min], [max], f_max.Ask [close] 
from 
(select 
   datepart(hh,timestamp) [hour], 
   min(High) [min], 
   max(Low) [max],
   min(id) min_id, 
   max(id) max_id 
 from forex 
 where Symbol ='EURUSD' and 
[timestamp] between '2013-07-18 01:00:00.000' and '2013-07-18 15:00:00.000'
 group by datepart(hh,timestamp) 
)  minmax 
join forex f_min on min_id = f_min.id
join forex f_max on max_id = f_max.id order by [hour]

Sql Server: Get OHLC in one query with n recodrs

2 Answers2