I'm trying to return a count by date range between min date and max date for projects. My table contains:
| 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 |
The expected result is:
| ProjectID | Sequence | StartDate | EndDate |
|---|---|---|---|
| 00025 | 01 | 2021-01-01 | 2021-02-28 |
| 00025 | 02 | 2021-03-01 | 2021-03-31 |
| 00025 | 02 | 2021-03-01 | 2021-03-31 |
| 00025 | 03 | 2021-04-01 | 2021-04-30 |
| 00025 | 03 | 2021-04-01 | 2021-04-30 |
| 00025 | 03 | 2021-04-01 | 2021-04-30 |
| 00259 | 01 | 2021-02-01 | 2021-02-28 |
| 00259 | 01 | 2021-02-01 | 2021-02-28 |
| 00037 | 01 | 2021-01-01 | 2021-01-31 |
| 00037 | 02 | 2021-02-01 | 2021-02-28 |
| 00037 | 03 | 2021-05-01 | 2021-06-30 |
I tried some queries and the most complex query that I've used without success was:
select
ProjectID
,count(1) as Sequence
,min(StartDate) as StartDate
,max(EndDate) as EndDate
from(
select
t.*
,row_number() over(order by EndDate) as sequence
,row_number() over(partition by RecordID order by EndDate) as sequence_t
from
dbo.ProjectTable as t
) as t
group by
ProjectID
,(sequence - sequence_t)
order by
min(EndDate)
I don't know how to return the expected value in sequence once count in this query and other queries that I tried return only 1 for each record.
Below the DDL
Create Table:
create table [dbo].[ProjectTable](
[RecordID] int NOT NULL,
[ProjectID] int NOT NULL,
[StartDate] date NULL,
[EndDate] date NULL
) on [primary]
go
Insert Data:
insert into [dbo].[ProjectTable] values
(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')
go