SQL Server 2019

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

CREATE TABLE #DueDates (OrderNo INT, DueDate Date, SysLogDate Date)
INSERT INTO #DueDates Values (1, '4/10/2022',   '1/10/2022')
                            ,(1, '4/10/2022',   '1/11/2022')
                            ,(1, '4/15/2022',   '1/15/2022')
                            ,(1,        NULL,   '1/16/2022')  -- Altered Due Date
                            ,(1, '4/15/2022',   '1/17/2022')
                            ,(1, '4/10/2022',   '1/18/2022')
                            ,(1, '4/10/2022',   '1/19/2022')
                            ,(1, '4/10/2022',   '1/20/2022')
                            ,(2, '4/10/2022',   '2/16/2022')
                            ,(2, '4/10/2022',   '2/17/2022')
                            ,(2, '4/15/2022',   '2/18/2022')
                            ,(2, '4/15/2022',   '2/20/2022')
                            ,(2, '4/15/2022',   '2/21/2022')
                            ,(2, '4/10/2022',   '2/22/2022')
                            ,(2, '4/10/2022',   '2/24/2022')
                            ,(2, '4/10/2022',   '2/26/2022')
16 rows affected

    select OrderNo, DueDate, SysLogDate, 
      dense_rank() over(partition by orderno order by gp) SectionNumber_WithinDueDate
    from (
      select *,
        Row_Number() over(partition by OrderNo order by SysLogDate)
        - Row_Number() over(partition by OrderNo, DueDate order by SysLogDate) gp
      from #DueDates
    )t
    order by OrderNo, SysLogDate;


OrderNo
DueDate
SysLogDate
SectionNumber_WithinDueDate


1
2022-04-10
2022-01-10
1

1
2022-04-10
2022-01-11
1

1
2022-04-15
2022-01-15
2

1
null
2022-01-16
3

1
2022-04-15
2022-01-17
3

1
2022-04-10
2022-01-18
3

1
2022-04-10
2022-01-19
3

1
2022-04-10
2022-01-20
3

2
2022-04-10
2022-02-16
1

2
2022-04-10
2022-02-17
1

2
2022-04-15
2022-02-18
2

2
2022-04-15
2022-02-20
2

2
2022-04-15
2022-02-21
2

2
2022-04-10
2022-02-22
3

2
2022-04-10
2022-02-24
3

2
2022-04-10
2022-02-26
3


select OrderNo, DueDate, SysLogDate, 
       SectionNumber_WithinDueDate = dense_rank() over (partition by OrderNo 
                                                            order by grp)
from
(
    select *, grp = sum(g) over (partition by OrderNo 
                                     order by SysLogDate)
    from   
    ( 
           select *,
                  g = case when isnull(DueDate, '99991231')
                           <>   isnull(lag(DueDate) over (partition by OrderNo 
                                                              order by SysLogDate), '99991231')
                           then 1
                           else 0
                           end
           from   #DueDates
   ) d
) d
order by OrderNo, SysLogDate;


OrderNo
DueDate
SysLogDate
SectionNumber_WithinDueDate


1
2022-04-10
2022-01-10
1

1
2022-04-10
2022-01-11
1

1
2022-04-15
2022-01-15
2

1
null
2022-01-16
3

1
2022-04-15
2022-01-17
4

1
2022-04-10
2022-01-18
5

1
2022-04-10
2022-01-19
5

1
2022-04-10
2022-01-20
5

2
2022-04-10
2022-02-16
1

2
2022-04-10
2022-02-17
1

2
2022-04-15
2022-02-18
2

2
2022-04-15
2022-02-20
2

2
2022-04-15
2022-02-21
2

2
2022-04-10
2022-02-22
3

2
2022-04-10
2022-02-24
3

2
2022-04-10
2022-02-26
3

OrderNo	DueDate	SysLogDate	SectionNumber_WithinDueDate
1	2022-04-10	2022-01-10	1
1	2022-04-10	2022-01-11	1
1	2022-04-15	2022-01-15	2
1	null	2022-01-16	3
1	2022-04-15	2022-01-17	3
1	2022-04-10	2022-01-18	3
1	2022-04-10	2022-01-19	3
1	2022-04-10	2022-01-20	3
2	2022-04-10	2022-02-16	1
2	2022-04-10	2022-02-17	1
2	2022-04-15	2022-02-18	2
2	2022-04-15	2022-02-20	2
2	2022-04-15	2022-02-21	2
2	2022-04-10	2022-02-22	3
2	2022-04-10	2022-02-24	3
2	2022-04-10	2022-02-26	3