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 |