How to query Open-high-low-close (OHLC) data from SQL Server

Question

Hi,

I'm trying to retrieve data for a Open-high-low-close (OHLC) chart directly from the database, it's the kind of chart you see of stocks. Is this possible, and if, how?

I have a table like this (simplified):

Date | Price | PriceType

A record is created for each day, I will report per month / year, not per day as used for stocks.

I would like to query something like this:

SELECT PriceType, MAX(Price) as High, MIN(Price) as Low, [Price of first item of month] as Open, [Price of last item of month] as Close GROUP BY PriceType, Year(Date), Month(Date)

To access the SQL Server I use LLBLGen, so an anwser based on that technology would be great, a generic SQL server will do too!

It's SQL 2005, but 2008 is also an option.

Thanks.

Answer 1

This appears to work. There may well be a less verbose way to do it.

--create test data
CREATE TABLE #t
(priceDate DATETIME
,price MONEY
,priceType CHAR(1)
)

INSERT #t
      SELECT '20090101',100,'A'
UNION SELECT '20090102',500,'A'
UNION SELECT '20090103',20 ,'A'
UNION SELECT '20090104',25 ,'A'
UNION SELECT '20090105',28 ,'A'
UNION SELECT '20090131',150,'A'


UNION SELECT '20090201',501,'A'
UNION SELECT '20090203',21 ,'A'
UNION SELECT '20090204',26 ,'A'
UNION SELECT '20090205',29 ,'A'
UNION SELECT '20090228',151,'A'


UNION SELECT '20090101',100,'B'
UNION SELECT '20090102',500,'B'
UNION SELECT '20090103',20 ,'B'
UNION SELECT '20090104',25 ,'B'
UNION SELECT '20090105',28 ,'B'
UNION SELECT '20090131',150,'B'


UNION SELECT '20090201',501,'B'
UNION SELECT '20090203',21 ,'B'
UNION SELECT '20090204',26 ,'B'
UNION SELECT '20090205',29 ,'B'
UNION SELECT '20090228',151,'B'

--query
;WITH rangeCTE
AS
(
        SELECT  MIN(priceDate) minDate
                ,MAX(priceDate) maxDate
        FROM #t
)
,datelistCTE
AS
(
        SELECT CAST(CONVERT(CHAR(6),minDate,112) + '01' AS DATETIME) AS monthStart
               ,DATEADD(mm,1,CAST(CONVERT(CHAR(6),minDate,112) + '01' AS DATETIME)) -1 AS monthEnd
               ,1 AS monthID
        FROM rangeCTE

        UNION ALL

        SELECT DATEADD(mm,1,monthStart)
               ,DATEADD(mm,2,monthStart) - 1
               ,monthID + 1
        FROM datelistCTE
        WHERE monthStart <= (SELECT maxDate FROM rangeCTE)
)
,priceOrderCTE
AS
(
        SELECT * 
               ,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
                                   ORDER BY priceDate
                                   ) AS rn1
               ,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
                                   ORDER BY priceDate DESC
                                   ) AS rn2
               ,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
                                   ORDER BY price DESC
                                   ) AS rn3                                   
               ,ROW_NUMBER() OVER (PARTITION BY monthID, priceType
                                   ORDER BY price 
                                   ) AS rn4
        FROM datelistCTE AS d
        JOIN #t          AS t
        ON t.priceDate BETWEEN d.monthStart AND d.monthEnd
        WHERE monthStart <= (SELECT maxDate FROM rangeCTE)
)
SELECT o.MonthStart
       ,o.priceType
       ,o.Price AS opening
       ,c.price AS closing
       ,h.price AS high
       ,l.price AS low
FROM priceOrderCTE AS o
JOIN priceOrderCTE AS c
ON   c.priceType = o.PriceType 
AND  c.monthID   = o.MonthID
JOIN priceOrderCTE AS h
ON   h.priceType = o.PriceType 
AND  h.monthID   = o.MonthID
JOIN priceOrderCTE AS l
ON   l.priceType = o.PriceType 
AND  l.monthID   = o.MonthID
WHERE o.rn1 = 1
AND   c.rn2 = 1
AND   h.rn3 = 1
AND   l.rn4 = 1