Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Stackoverflow 37635278
--Sql Server 2014 Express Edition select @@version as 'sql server version' create table T ([Date] datetime not null, Price decimal(4, 1) not null, Valuation int not null); insert into T ([Date], Price, Valuation) values ('06/01/2016', 9.2, 9), ('05/31/2016', 9.3, 5), ('05/27/2016', 9.5, 8), ('05/26/2016', 9.7, 7), ('05/25/2016', 9.5, 8), ('05/24/2016', 9.3, 4), ('05/23/2016', 9.2, 3), ('05/20/2016', 9.2, 5), ('05/19/2016', 8.9, 9), ('05/18/2016', 9.1, 4), ('05/17/2016', 9.2, 9); select * from T where [Date] in ( select min(t2.[Date]) from T t1 inner join T t2 on t2.[Date] > t1.[Date] where t1.Valuation > 5 group by t1.[Date] ); select * from T where [Date] in ( select t2.[Date] from T t1 inner join T t2 on t2.[Date] > t1.[Date] and t2.[Date] < dateadd(dd, 7, t1.[Date]) /* bound for performance */ where /* valuation starts at or below 5 */ t1.Valuation <= 5 /* did we find the day after the next day? */ and 3 = ( select count(*) from T t3 where t3.[Date] between t1.[Date] and t2.[Date] ) /* did the valuation rise above 5 on the day in between */ and 5 < ( select t4.Valuation from T t4 where t4.[Date] > t1.[Date] and t4.[Date] < t2.[Date] ) );
run
|
edit
|
history
|
help
0
Q2_30min
for xml path concatenation ordered by seqno
TEST 2
Mc170404738
Ggg
Lisber-SQL-Example(CASE)
exp7
Grouping, aggregate function issue when joining to another table
Varchar to Binary and vice-versa
Training1