clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 921276 fiddles created (11971 in the last week).

create table orders (OrderDate date, OrderCount int); insert into orders values ('2020-01-12', 0), ('2020-01-13', 17), ('2020-01-14', 22), ('2020-01-15', 21), ('2020-01-16', 30), ('2020-01-17', 12), ('2020-01-18', 0), ('2020-01-20', 0), ('2020-01-21', 49), ('2020-01-22', 28), ('2020-01-23', 4);
11 rows affected
 hidden batch(es)


select OrderDate, lag(OrderDate) over (order by OrderDate) lag, datediff(day, coalesce(lag(OrderDate) over (order by OrderDate), dateadd(day, 1, OrderDate)), OrderDate ) diff, case when datediff(day, coalesce(lag(OrderDate) over (order by OrderDate), OrderDate), OrderDate ) <> 1 then 1 else 0 end ld from orders ;
OrderDate lag diff ld
2020-01-12 -1 1
2020-01-13 2020-01-12 1 0
2020-01-14 2020-01-13 1 0
2020-01-15 2020-01-14 1 0
2020-01-16 2020-01-15 1 0
2020-01-17 2020-01-16 1 0
2020-01-18 2020-01-17 1 0
2020-01-20 2020-01-18 2 1
2020-01-21 2020-01-20 1 0
2020-01-22 2020-01-21 1 0
2020-01-23 2020-01-22 1 0
 hidden batch(es)


declare @date date = '2020-01-16'; with ct as ( select OrderDate, case when datediff(day, coalesce(lag(OrderDate) over (order by OrderDate), OrderDate), OrderDate ) <> 1 then 1 else 0 end rst from orders where OrderDate <= @date ) , ct2 as ( select OrderDate, sum(rst) over (order by OrderDate) as grp from ct ) select top 1 count(OrderDate) streak from ct2 group by grp order by max(OrderDate) desc
streak
5
 hidden batch(es)