SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

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
null
-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


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

OrderDate	lag	diff	ld
2020-01-12	null	-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