SQl 2012 FIRST_Value and Last_Value SubQuery Not Working
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.
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, UTCDateTimeASKER
Hello hnasr.. i am not sure how that will work. I am attempting to take the average of those two fields and then use it to represent 4 new fields. Open, High, Low and Close.
This data is being converted to the timeframe of 1 minute so i was grouping based on UTCdatetime. If I understand what you are saying by grouping by Bid/Ask I would end up with multiple records per minute as each price may change multiple times during the minute.
I was also trying to avoid having to run a second view as I have 100 of these little buggers.
This data is being converted to the timeframe of 1 minute so i was grouping based on UTCdatetime. If I understand what you are saying by grouping by Bid/Ask I would end up with multiple records per minute as each price may change multiple times during the minute.
I was also trying to avoid having to run a second view as I have 100 of these little buggers.
What is the expected output?
The attached sheet, shows the raw data.
Upload same excel by adding a sheet, or using the same sheet , showing the expected output after applying the query.
The attached sheet, shows the raw data.
Upload same excel by adding a sheet, or using the same sheet , showing the expected output after applying the query.
ASKER
Hello hnasr. like this. same in new spreadsheet..thankyou
C(ID) Instrument O H L C V UTC
5 AUD/CAD 0.981405 0.981455 0.981395 0.981455 85 47:00.0
6 AUD/CAD 0.981545 0.981545 0.981465 0.98149 73 48:00.0
6 AUD/CAD 0.981505 0.981505 0.98139 0.981395 86 49:00.0
The UTC will be proper as per sql datetime, it is purely just the query structure to be able to integrate the First and Last methods using sql 2012, for the calculated "MID" column as calculated in the new SS
TickData3MinutesWorth-Calculated.xlsx
C(ID) Instrument O H L C V UTC
5 AUD/CAD 0.981405 0.981455 0.981395 0.981455 85 47:00.0
6 AUD/CAD 0.981545 0.981545 0.981465 0.98149 73 48:00.0
6 AUD/CAD 0.981505 0.981505 0.98139 0.981395 86 49:00.0
The UTC will be proper as per sql datetime, it is purely just the query structure to be able to integrate the First and Last methods using sql 2012, for the calculated "MID" column as calculated in the new SS
TickData3MinutesWorth-Calculated.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The solution was found by using a modified approach to that of the original sample.. Thankyou for your contributions and time.
Other issue, the select statement for FIRST_VALUE should return one value when it is selected with * (all fields).