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
,DENSE_RANK() Over (Partition By OrderNo Order By grp) as SectionNumber_WithinDueDate
From (Select *
,SUM(g) Over (Partition By OrderNo Order By SysLogDate) as grp
From (Select *
,Case When ISNULL(DueDate, '99991231') <> ISNULL(LAG(DueDate) Over (Partition By OrderNo Order By SysLogDate), '99991231') Then 1
Else 0
End as g
From #DueDates
) a
) b
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 |
;With CTE1 as (
Select *
,LAG(DueDate) Over (Partition By OrderNo Order By SysLogDate) as DueDate_Lagged
From #DueDates
),CTE2 as (
Select *
,Case When ISNULL(DueDate, '12/31/9999') <> ISNULL(DueDate_Lagged, '12/31/9999') Then 1
Else 0
End as g
From CTE1
),CTE3 as (
Select *
,SUM(g) Over (Partition By OrderNo Order By SysLogDate) as grp
From CTE2
)
Select OrderNo
,DueDate
,SysLogDate
,DENSE_RANK() Over (Partition By OrderNo Order By grp) as SectionNumber_WithinDueDate
From CTE3
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 |