1: you can use one statement to solve it, stored procedure is not necessary Select * from stockquotes a where
(
select close from stockquotes where tradedate
= (select max(tradedate) from stockquotes where tradedate<a.tradedate)
) < close
(
select close from stockquotes where tradedate
= (select max(tradedate) from stockquotes where tradedate<a.tradedate)
) < close
tradedate, symbol ,open, close, high, low.
Write a stored procedure that selects all the stocks(symbol) where a certain day's close is higher
than the previous trading day's (not necessarily the previous day because of holidy and weekend) high.
*/
create proc usp_getsymbol
as
set nocount on
begin
select * from stockquotes a
inner join stockquotes b on a.symbol=b.symbol
where b.[close]>=a.tradedate
end
as
set nocount on
begin
select * from stockquotes a
inner join stockquotes b on a.symbol=b.symbol
where a.[close]>=b.[close]
and a.tradedate>b.tradedate
end
@date datetime
as
begin
select * from stockquotes tem where datediff(day,tem.tradedate,@date)=0 and exists(select 1 from stockquotes where tradedate=(select max(tradedate) from stockquotes where datediff(day,tradedate,@date)>0) and symbol=tem.symbol and [close]<[tem.close])
end
go---
declare @ datetime
set @=getdate()
exec getsymbol @
@date datetime
as
begin
select * from stockquotes tem where datediff(day,tem.tradedate,@date)=0 and exists(select 1 from stockquotes where tradedate=(select max(tradedate) from stockquotes where datediff(day,tradedate,@date)>0) and symbol=tem.symbol and [close]<tem.[close])
end
go---
declare @ datetime
set @=getdate()
exec getsymbol @