OHCL (Open-high-low-close) T-SQL Query

Question

I'm trying to do a OHCL sql query (SQL Server 2012) on a StockData table. There are thousands of rows per day added to the table and I want to get the Open, High, Low and Close data for each day.

The create table sql is below:

  CREATE TABLE [dbo].[StockData](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [StockExchangeID] [bigint] NOT NULL,
    [DataDateTime] [datetime] NOT NULL,
    [ExportCost] [bigint] NOT NULL,
 CONSTRAINT [PK_StockData] 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]

GO

Using the query below I can get the High, Low and Average but I'm having difficulty getting the Open (earliest ExportCost based on DataDateTime) and Close (latest ExportCost based on DataDateTime) of the ExportCost for the day.

My query so far is below. If there is a more efficient way to do it then any suggestions welcome.

DECLARE @IntervalDays INT, @StartDate DATETIME, @EndDate DATETIME;
SET @IntervalDays = 1;
SET @StartDate = '01/01/2017'
SET @EndDate = '01/01/2018'

-- Create the database table
SELECT 
    StockExchangeID,
    DATEADD(DAY, DATEDIFF(DAY,0,[DataDateTime]) / @IntervalDays * @IntervalDays, 0) AS [DateDay],
    MAX(ExportCost) AS DayHigh, 
    MIN(ExportCost) AS DayLow, 
    AVG(ExportCost) AS DayAverage,
    '??????????' As DayOpen, --Not sure how to get the start price for the day i.e. ExportCost for the first record for that day based on [DataDateTime]
    '??????????' As DatClose
FROM StockData
WHERE [DataDateTime] >= @StartDate AND [DataDateTime] <= @EndDate
GROUP BY DATEADD(DAY, DATEDIFF(DAY,0,[DataDateTime]) / @IntervalDays * @IntervalDays, 0), StockExchangeID
ORDER BY [DateDay]

Answer 1

For older versions of SQL Server use can use a correlated subquery:

SELECT 
    StockExchangeID,
    DATEADD(DAY, DATEDIFF(DAY,0,[DataDateTime]) / @IntervalDays * @IntervalDays, 0) AS [DateDay],
    MAX(ExportCost) AS DayHigh, 
    MIN(ExportCost) AS DayLow, 
    AVG(ExportCost) AS DayAverage,
    (SELECT TOP 1 DO.ExportCost FROM StockData AS DO WHERE DO.[DataDateTime] = MIN( A.[DataDateTime] ) ORDER BY ID ASC ) As DayOpen, 
    (SELECT TOP 1 DC.ExportCost FROM StockData AS DC WHERE DC.[DataDateTime] = MAX( A.[DataDateTime] ) ORDER BY ID DESC ) As DatClose
FROM StockData AS A
WHERE [DataDateTime] >= @StartDate AND [DataDateTime] <= @EndDate
GROUP BY DATEADD(DAY, DATEDIFF(DAY,0,[DataDateTime]) / @IntervalDays * @IntervalDays, 0), StockExchangeID
ORDER BY [DateDay]

Note: Order By ID is used just in case you have more than 1 record with the same date.

For newer versions, take a look at @Serg comment about FIRST_VALUE