CODEWITHSUNDEEP
sqlsql-servert-sqldate-range
jmich738
jmich738

Reputation: 1675

SQL - Count number of itemactive within a date rate

I have a dataset of Resources, Projects, StartDate and EndDate. Each Resource can be utilised by multiple projects.

I want to get a count of the number of projects that are using a resource in each quarter.

So if project starts in Q1 of a particular year and ends in Q3 that year, and project2 starts in Q2 and ends in Q3, I want to get a count of 2 projects for Q2, since during Q1 both project1 and project2 were active.

Here is my dataset:

create table Projects
(Resource_Name varchar(20)
,Project_Name varchar(20)
,StartDate varchar(20)
,EndDate varchar(20)
)


insert into Projects values('Resource 1','Project A','15/01/2013','1/11/2014')
insert into Projects values('Resource 1','Project B','1/03/2013','1/09/2016')
insert into Projects values('Resource 1','Project C','1/04/2013','1/09/2015')
insert into Projects values('Resource 1','Project D','1/06/2013','1/03/2016')
insert into Projects values('Resource 1','Project E','15/01/2013','1/09/2015')
insert into Projects values('Resource 1','Project F','3/06/2013','1/11/2015')

And here is the result I'm looking for:

Resource Name| Year | Quarter|Active Projects
Resource 1     2013     1           2
Resource 1     2013     2           6

Upvotes: 3

Views: 398

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Using tally table:

Using the dates from Projects, generate a list of all quarters and their start dates and end dates, in this example, that is CteQuarter(sd, ed). After that, you simply need to JOIN the Projects table to CteQuarter for overlapping dates. Then finally, GROUP BY using the YEAR and Quarter part of the date.

SQL Fiddle

WITH CteYear(yr) AS(
    SELECT number
    FROM master..spt_values
    WHERE 
        type = 'P'
        AND number >= (SELECT MIN(YEAR(CONVERT(DATE, StartDate, 103))) FROM Projects)
        AND number <= (SELECT MAX(YEAR(CONVERT(DATE, EndDate, 103))) FROM Projects)
),
CteQuarter(sd, ed) AS(
    SELECT
        DATEADD(QUARTER, q.n - 1, DATEADD(YEAR, cy.yr - 1900, 0)),
        DATEADD(DAY, -1, DATEADD(QUARTER, q.n, DATEADD(YEAR, cy.yr - 1900, 0)))
    FROM CteYear AS cy
    CROSS JOIN(
        SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    ) AS q(n)
)
SELECT 
    p.Resource_Name,
    [Year] = DATEPART(YEAR, q.sd),
    [Quarter] = DATEPART(QUARTER, q.sd),
    [Active Projects] = COUNT(*)
FROM Projects p
INNER JOIN CteQuarter q
    ON CONVERT(DATE, StartDate, 103) <= q.ed
    AND CONVERT(DATE, EndDate, 103) >= q.sd
GROUP BY 
    p.Resource_Name,
    DATEPART(YEAR, q.sd),
    DATEPART(QUARTER, q.sd)
ORDER BY
    p.Resource_Name,
    DATEPART(YEAR, q.sd),
    DATEPART(QUARTER, q.sd)

Notes:

  1. Here is a great way to check for overlapping dates.
  2. Some common date routines

RESULT:

| Resource_Name | Year | Quarter | Active Projects |
|---------------|------|---------|-----------------|
|    Resource 1 | 2013 |       1 |               3 |
|    Resource 1 | 2013 |       2 |               6 |
|    Resource 1 | 2013 |       3 |               6 |
|    Resource 1 | 2013 |       4 |               6 |
|    Resource 1 | 2014 |       1 |               6 |
|    Resource 1 | 2014 |       2 |               6 |
|    Resource 1 | 2014 |       3 |               6 |
|    Resource 1 | 2014 |       4 |               6 |
|    Resource 1 | 2015 |       1 |               5 |
|    Resource 1 | 2015 |       2 |               5 |
|    Resource 1 | 2015 |       3 |               5 |
|    Resource 1 | 2015 |       4 |               3 |
|    Resource 1 | 2016 |       1 |               2 |
|    Resource 1 | 2016 |       2 |               1 |
|    Resource 1 | 2016 |       3 |               1 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270643

You can do this by determining the first and last quarters when a project is active, and then using cumulative sum. In SQL Server 2012+, this looks like

select resource_name, yyyyq, 
       (sum(sum(s)) over (partition by resource_name order by yyyyq) -
        sum(sum(e)) over (partition by resource_name order by yyyyq) +
        e
       ) as activeProjects
from ((select resource_name, datepart(year, startdate) + datepart(quarter, startdate) as yyyyq, 1 as s, 0 as e
       from projects
      ) union all
      (select resource_name, datepart(year, enddate) + datepart(quarter, enddate), 0 as s, 1 as e
       from projects
      )
     ) yq
group by resource_name, yyyyq;

In earlier versions, you can do something similar with cross apply.

Upvotes: 0

Related Questions