按最小日期和最大日期之间的日期范围计数

Question

我试图按项目的最小日期和最大日期之间的日期范围返回计数。我的表包含:

tbody> <<tr>

RecordID	ProjectID	StartDate	EndDate
1254879	00025	2021-01-01	2021-02-28
1254248	00025	2021-03-01	2021-03-31
1253598	00025	2021-03-01	2021-03-31
1253578	00025	2021-04-01	2021-04-30
1253698	00025	2021-04-01	2021-04-30
1254873	00025	2021-04-01	2021-04-30
1253697	00259	2021-02-01	2021-02-28
1253570	00259	2021-02-01	2021-02-28
1254999	00037	2021-01-01	2021-01-31
1252695	00037	2021-02-01	2021-02-28
1254889	00037	2021-05-01	2021-06-30

我相信您想要dense_rank()窗口函数:

select ProjectId, StartDate, EndDate
, dense_rank() over (partition by ProjectId order by StartDate)
from dbo.ProjectTable
order by ProjectId, StartDate, EndDate;