SELECT 	FIRST_VALUE(Price) OVER (partition by Symbol order by TradeTime) AS [FIRST],
	FIRST_VALUE(Price) OVER (partition by Symbol order by TradeTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [LAST],
	min(Price) OVER (partition by Symbol) AS [MIN],
	max(Price) OVER (partition by Symbol) AS [MAX],
....
FROM  my_table_of_stock_trades
	WHERE 	Symbol	= 'IBM' 
	and 	TradeTime > '01/01/2013 12:00:00' 
	AND 	TradeTime < '01/01/2013 12:15:00'	

quote:

---

Originally posted by khtan

; with cte as
(
	select	*, 
		rn = row_number() over (partition by Symbol order by TradeTime), 
		cnt = count(*) over (partition by Symbol)
	from	my_table_of_stock_trades
	WHERE 	Symbol	= 'IBM' 
	and 	TradeTime > '01/01/2013 12:00:00' 
	AND 	TradeTime < '01/01/2013 12:15:00'
)
SELECT 	[FIRST] = max(case when rn = 1 then Price end),
	[LAST]	= max(case when rn = cnt then Price end),
	[MIN]	= min(Price),
	[MAX]	= max(Price)
FROM 	cte 

---

quote:

---

Originally posted by ryan_hunter1200

khtan,

thank you for the sql query. this works very well!

I am trying to modify this for my GROUP BY exchange (so I group by symbol -- so i do this for every symbol in the table for the time period and not just 'IBM') but I cannot figure it out.

SELECT Symbol, FIRST(Price), LAST(Price), MAX(Price), MIN(Price) FROM my_table_of_stock_trades WHERE TradeTime > '01/01/2013 12:00:00' AND TradeTime < '01/01/2013 12:15:00' GROUP BY Symbol

how do I use your approach for the group by?

The Row_number() OVER (Partition ..) is something i saw when I googled this issue but i have to admit it is very confusing. with your working example, I find this is very helpful, so thank you very much.

Can you kindly help with a GROUP BY solution? the only solution I have right now is to use a CURSOR over every DISTINCT(Symbol) during the time period and run your query. But this is much too expensive. If I can modify your query to return first/last/high/low for each distinct symbol during the time period that would be precise. thank you.

quote:

---

Originally posted by khtan

; with cte as
(
	select	*, 
		rn = row_number() over (partition by Symbol order by TradeTime), 
		cnt = count(*) over (partition by Symbol)
	from	my_table_of_stock_trades
	WHERE 	Symbol	= 'IBM' 
	and 	TradeTime > '01/01/2013 12:00:00' 
	AND 	TradeTime < '01/01/2013 12:15:00'
)
SELECT 	[FIRST] = max(case when rn = 1 then Price end),
	[LAST]	= max(case when rn = cnt then Price end),
	[MIN]	= min(Price),
	[MAX]	= max(Price)
FROM 	cte 

---

---

quote:

---

Originally posted by ryan_hunter1200

Hi Visakh16,

I did not try your solution because I do not have SQL 2012 installed. I obviously would prefer to stick with sql2008 if I can (assuming 2012 has some performance benefit for this type of query).

Do you think the other solution can be tweaked to work with a GROUP BY or do I need to upgrade to 2012 to do what I am looking for?

---

; with cte as
(
	select	*, 
		rn = row_number() over (partition by Symbol order by TradeTime), 
		cnt = count(*) over (partition by Symbol)
	from	my_table_of_stock_trades
	WHERE 	Symbol	= 'IBM' 
	and 	TradeTime > '01/01/2013 12:00:00' 
	AND 	TradeTime < '01/01/2013 12:15:00'
)
SELECT 	Symbol,
        [FIRST] = max(case when rn = 1 then Price end),
	[LAST]	= max(case when rn = cnt then Price end),
	[MIN]	= min(Price),
	[MAX]	= max(Price)
FROM 	cte
GROUP BY Symbol