I am trying to implement a First_Value and Last_Value Query for TICK Data
My SQL falls over when trying to generate my Open (O) and Close (C) columns.
I have attached 3 minutes of the View data to test. The data is being put in the minute timeframe based on the UTC column.
Pls advise where my syntax is failing with the nested query. I am aiming for 3 lines output with this sample.
SELECT Count(Id) as Count,Instrument, (MAX(Bid) + MAX(Ask)) / 2 AS H, (MIN(Bid) + MIN(Ask)) / 2 AS L, UTCDateTime AS UTC, SUM(CAST(Volume AS int)) AS V
FROM (SELECT FIRST_VALUE((MAX(Bid) + MAX(Ask)) / 2) OVER (order BY UTCDateTime) as O, * FROM dbo.Vw_AUD_CAD) AS subqry
GROUP BY Instrument, UTCDateTime