CODEWITHSUNDEEP
sqlsql-servert-sql
ito
ito

Reputation: 157

Count by date range between min date and max date

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

Upvotes: 2

Views: 474

Answers (1)

Dale K
Dale K

Reputation: 27202

I believe you want the dense_rank() window function:

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

Upvotes: 1

Related Questions