Stackoverflow 37635278, Sql Server

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